poi知识积累

导出需要的头:response.setContentType("octets/stream;charset=utf-8");     (需要导出的 类型都是可以在tomcat---conf下的web.xml中找到)
         response.setHeader("Content-Disposition","attachment;filename=a.xls");

提示:多报表导出在一格sheet页中,固定行数可以采取定位某行的方法;

 如果不确定,最好是采取直接写入,从上往下,或者从左往右写.

获取(cell)单元格中内容的方法:

//获取行中的列

HSSFRow dataRow = sheet.getRow(i);
HSSFCell dataCell = dataRow.getCell(0);
String firstCell = ExcelUtil.ReadCell(dataCell);

//ReadCell方法  待定

//数字保留几位小数     String valD = String.format("%.5f", val);       (此处保留val的五位小数,其中val为double类型返回的是String类型)

1,合并单元格

sheet.addMergedRegion(new Region(0, (short) (celln + 1), 0,  (short) (celle + 1)));   四个参数分别代表起始行,起始列,结束行,结束列.

2,一格格插入

 public static void setCellValue(HSSFSheet sheet,int iRow, int iColumn, String values,
			HSSFCellStyle style) throws Exception {
    	try {
			HSSFRow row = sheet.getRow(iRow);
				row = sheet.createRow(iRow);
			HSSFCell cell = row.getCell((short) iColumn);
				cell = row.createCell((short) iColumn);
//			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(values);
			cell.setCellStyle(style);
		} catch (Exception e) {

			throw e;
		}
    }

3,创建(cell)单元格的格式

先获取工作薄对象:

HSSFWorkbook wb = new HSSFWorkbook();


HSSFSheet sheet = wb.createSheet();


HSSFCellStyle setBorder = wb.createCellStyle();


一、设置背景色：

setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框


三、设置居中:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中


四、设置字体:

HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小

HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行

七 、没有模板输出Excel

response.setContentType("octets/stream;charset=utf-8");
String str=new String("车身.xls".getBytes(),"iso-8859-1");
System.out.println(str);
response.setHeader("Content-Disposition","attachment;filename="+str);
OutputStream out =response.getOutputStream();
HSSFWorkbook work=new HSSFWorkbook();
HSSFCellStyle style=work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillBackgroundColor((short)23);
HSSFFont font=work.createFont();
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
HSSFSheet sheet1=work.createSheet("车身");
sheet1.setDefaultColumnWidth(15);

八.带模板输出Excel

//获取模板
public HSSFWorkbook   getTemplate(String filepath) {
FileInputStream fis = null;
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fis   = new FileInputStream(filepath);
fs = new POIFSFileSystem(fis);
wb = new HSSFWorkbook(fs);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wb;
}

      // 导出

response.setCharacterEncoding("UTF-8");
response.setContentType("application/msexcel");
String strFileName = java.net.URLEncoder.encode("东风,毛利表.xls", "UTF-8");
response.setHeader("Content-disposition", "attachment; filename="
+ strFileName);
   HSSFWorkbook work = this.getTemplate(request.getRealPath("/")+"resource/excel/dpmvalue.xls");
   HSSFSheet sheet=work.getSheetAt(0);
   HSSFCellStyle style=work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font=work.createFont();
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
ExcelUtil.setCellValue(sheet, 0, 0, "净现值和MOC(%)演变 Evolution de VAN", style);

//setCellValue 方法

 public static void setCellValue(HSSFSheet sheet,int iRow, int iColumn, String values,
HSSFCellStyle style) throws Exception {
    try {
HSSFRow row = sheet.getRow(iRow);
if (null == row) {
row = sheet.createRow(iRow);
}
HSSFCell cell = row.getCell((short) iColumn);
if (cell == null) {
cell = row.createCell((short) iColumn);
}
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (values == null) {
values = "";
}
cell.setCellValue(values);
cell.setCellStyle(style);
} catch (Exception e) {
// this.close();
throw e;
}
    }