Poi导出Excel

我用的是poi-3.16版本来做导出，下面是我做导出遇到的一些问题：

poi老版本上下左右边框切换高版本解决方法

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setBorderTop(HSSFCellStyle.BORDER_THIN);

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

//新版本的

cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框

cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框

cellStyle.setBorderTop(BorderStyle.THIN);//上边框

cellStyle.setBorderBottom(BorderStyle.THIN);//下边框

 

老版本水平居中和垂直居中：

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 垂直居中

poi新版本的水平居中和垂直居中：

cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中  //最新版本的

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

Poi导出老版本字体颜色切换高版本的解决方案：

老版本：

font.setColor(HSSFColor.VIOLET.index);

新版本：

font.setColor(HSSFFont.COLOR_NORMAL); //字体颜色

Poi导出老版本的加粗切换高版本的解决方案

新版本：

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

新版本：

font.setBold(true); //是否 加粗   

Apache.poi合并单元格低版本切换高版本解决方法

合并单元格老版本:

此方法在POI3.8中已经被废弃

Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号

新版本：

sheet.addMergedRegion(new CellRangeAddress(1, (short) 1, 1, (short) 2));

//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列但应注意两个构造方法的参数不是一样的，具体使用哪个取决于POI的不同版本。

 

1. 在util包里面封装poi导出的方法：

package com.gx.util;

import java.io.IOException;

import java.io.OutputStream;

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.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.BorderStyle;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.gx.po.B_CostResolve;

 

public class CleanExcel {

         public void exportExcel(String title, String[] headers, List mapList,

                            OutputStream out, String pattern) {

                   // 声明一个工作簿  

                   HSSFWorkbook workbook = new HSSFWorkbook();

                   // 生成一个表格  

                   HSSFSheet sheet = workbook.createSheet(title);

                   // 设置表格默认列宽度为15个字符  

                   sheet.setDefaultColumnWidth(20);

                   // 生成一个样式，用来设置标题样式  

                   HSSFCellStyle style = workbook.createCellStyle();

                   // 设置这些样式  

                   style.setFillForegroundColor((short) 13);// 设置背景色

                   style.setBorderLeft(BorderStyle.THIN);// 设置左边框

                   style.setBorderRight(BorderStyle.THIN);// 设置右边框

                   style.setBorderTop(BorderStyle.THIN);// 上边框

                   style.setBorderBottom(BorderStyle.THIN);// 下边框;

                   style.setAlignment(HorizontalAlignment.CENTER);//

                   // 生成一个字体  

                   HSSFFont font = workbook.createFont();

                   font.setColor(HSSFFont.COLOR_NORMAL); // 字体颜色

                   font.setBold(true);

                   // 把字体应用到当前的样式  

                   style.setFont(font);

                   //  生成并设置另一个样式,用于设置内容样式  

                   HSSFCellStyle style2 = workbook.createCellStyle();

                   style2.setFillForegroundColor((short) 13);// 设置背景色

                   style2.setBorderLeft(BorderStyle.THIN);// 设置左边框

                   style2.setBorderRight(BorderStyle.THIN);// 设置右边框

                   style2.setBorderTop(BorderStyle.THIN);// 上边框

                   style2.setBorderBottom(BorderStyle.THIN);// 下边框;

                   style2.setAlignment(HorizontalAlignment.CENTER);//水平居中

                   style2.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

                   //  生成另一个字体  

                   HSSFFont font2 = workbook.createFont();

                   font2.setBold(true);

                   //  把字体应用到当前的样式

                   style2.setFont(font2);

                   // 产生表格标题行  

                   HSSFRow row = sheet.createRow(0);

                   for (int i = 0; i < headers.length; i++) {

                            HSSFCell cell = row.createCell(i);

                            cell.setCellStyle(style);

                            HSSFRichTextString text = new HSSFRichTextString(headers[i]);

                            cell.setCellValue(text);

                   }

                   B_CostResolve b_CostResolve = null;

                   for (int i = 0; i < mapList.size(); i++) {

                            b_CostResolve = (B_CostResolve) mapList.get(i);

                            row = sheet.createRow(i + 1);

                            int j = 0;

                            Object value = null;

                            // 设置要导出的第一个值

                            value = b_CostResolve.getCostResolveName();

                            row.createCell(j++).setCellValue(String.valueOf(value));// j=0

                            // 设置要导出的第二个值

                            value = b_CostResolve.getCostSubject();

                            row.createCell(j++).setCellValue(String.valueOf(value));// j=1

                            // 设置要导出的第三个值

                            value = b_CostResolve.getBudgetType();

                            row.createCell(j++).setCellValue(String.valueOf(value));

                   }

                   try {

                            workbook.write(out);

                   } catch (IOException e) {

                            e.printStackTrace();

                   }

         }

}

1. 然后在servlet调用封装好的导出方法:

public void adoExcel(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {

response.setContentType("octets/stream");

String excelName = "费用项信息";

// 转码防止乱码  response.addHeader("Content-Disposition", "attachment;filename="+ new String(excelName.getBytes("gb2312"), "ISO8859-1")+ ".xls");

                            String[] headers = new String[] { "费用项", "科目", "预算类型" };

                            try {

                                     OutputStream out = response.getOutputStream();

                                      CostResolveDao list = new CostResolveDaoImpl();//写一个查询要导出的字段

                                     List<B_CostResolve> b_CostResolves = list.selectAll();//自己封装的po类

                                     CleanExcel cleanExcel=new CleanExcel();//实例化封装的类

                                     cleanExcel.exportExcel(excelName, headers, b_CostResolves, out, "yyyy-MM-dd");

                                     out.close();

                                     System.out.println("excel导出成功！");

                            } catch (FileNotFoundException e) {

                                     e.printStackTrace();

                            } catch (IOException e) {

                                     e.printStackTrace();

                            }

                   }

1. 通过用键值对的方式在doPost里面定义key的形式,传到jsp页面去写value

String type = request.getParameter("type");

if ("login".equals(type)) {

login(request, response);

else if ("doExcel".equals(type)) {

                            adoExcel(request, response);

                   }

== 在基本数据类型的时候就是比较值是否相同,在引用类型时是比较地址

equals 用于判断引用类型的值

最简答来说除了8中基本类型以外剩下的都是引用类型

1. Jsp页面写一个导出按钮的点击事件：用servlet传过来的key，写这个value的方法，传到servlet去写doExcel方法

           $("#leadingout").click(function(){

             window.open("${ctx}/servlet/LoginYeMian?type=doExcel");

          });