poi 定制excel

定制03excel文件，并设置打印格式 附件为poijar包支持excel03、07 word03及源代码文件

@param columnNameStr 示例 元素为列名称及列宽

String []columnNameStr = {"序号,4","单位名称,11","姓名,6","性别,5",
"出生日期（年月日）,8","test1,4","tets2,4",
"test3,5","test4,8","审批日期,8",
"金额,4","上报人,5","经办人,5","资金来源,4",
"困难类型,4","备注,11","身份证号,17"};

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class DatatoExcel {

/**
* @param sheetName 工作簿的名称String
* @param columnNameStr String型数组，存储列名称及列宽数组    String[]
* @param valuelist 其元素为一行中各个单元格的值组成的string型list    List
* @param fileUrl excel文件存储名称及路径  例："E:\\person.xls"    String
* @return success 0表示因为文件已存在，1表示输出到excel失败，2表示操作成功
*/
//表格定制
@SuppressWarnings("deprecation")
public  static int create03ExcelWrite(
String sheetName,
String [] columnNameStr,
List<List<String>> valueList,
String fileUrl) throws Exception{
int success = 2;
File file = new File(fileUrl);
if(file.exists()){
System.out.println("文件已存在") ;
success = 0;
}
else{
final int columnNum = columnNameStr.length; //总列数
int rowNum = 0;

// 创建Excel文档
HSSFWorkbook wb = new HSSFWorkbook() ;
HSSFSheet sheet = wb.createSheet(sheetName) ;

//定制表头字体字号
HSSFFont font_head=wb.createFont();
font_head.setFontName("宋体");
font_head.setFontHeightInPoints((short)9);
font_head.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗

//定制内容字体字号
HSSFFont font_context=wb.createFont();
font_context.setFontName("宋体");
font_context.setFontHeightInPoints((short)9);

HSSFCellStyle style_head = wb.createCellStyle();
style_head.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
style_head.setFont(font_head);
style_head.setWrapText(true);//自动换行

HSSFCellStyle style_context = wb.createCellStyle();
style_context.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
style_context.setFont(font_context);
style_context.setWrapText(true);//自动换行

HSSFRow firstrow = sheet.createRow(0);
firstrow.setHeightInPoints(30);//设置行高
HSSFCell[] firstColumnCell = new HSSFCell[columnNum];

if(fileUrl != null){
//表格头单元赋值
for(int i= 0; i<columnNum; i++){
String[] value = columnNameStr[i].split(",");
firstColumnCell[i] = firstrow.createCell((short)i);
firstColumnCell[i].setCellValue(value[0]);
firstColumnCell[i].setCellStyle(style_head);
sheet.setColumnWidth(i,  new Integer(value[1])*333);  //设置宽度
System.out.println(value[0]) ;
}
rowNum ++;

//填充记录
for(List<String> rowValue :valueList){

HSSFRow row = sheet.createRow(rowNum) ; // 下标为1的行开始
row.setHeightInPoints(15);//设置行高
row.createCell((short) 0).setCellValue(rowNum);//序号列的值
int j = 1;
for(String cellValue :rowValue){
cellValue += "";
HSSFCell cell = row.createCell((short) j) ;
cell.setCellValue(new HSSFRichTextString(cellValue)) ;
cell.setCellStyle(style_context);
j ++ ;
System.out.println(cellValue) ;
}
rowNum ++;
}

//设置打印格式
wb.setPrintArea(0, 0, columnNum-3, 0, rowNum-1);//设置打印区域
wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 0);//设置每页的表头
sheet.setPrintGridlines(true);//全框打印
sheet.setRowBreak(25);//每页显示25条记录
sheet.setColumnHidden(columnNum-2, true);//设置打印隐藏域
sheet.setColumnHidden(columnNum-1, true);//设置打印隐藏域
HSSFPrintSetup print = sheet.getPrintSetup();
print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张类型：A4打印
print.setLandscape(true);//打印方向：横向打印

// 创建文件输出流，准备输出电子表格
try{
OutputStream out = new FileOutputStream(fileUrl) ;
wb.write(out) ;
out.close() ;
success = 2;
System.out.println("数据导入到excel成功") ;

}catch(Exception e){
e.printStackTrace();
success = 1;
}
}
}
return success;
}
}
[align=left][/align]