java poi 导出成excel

package ebc.base.common;import java.io.IOException;import java.io.OutputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;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.HSSFFooter;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.hssf.util.HSSFColor;public class ExportExcelUtil {private static Connection conn ;     private static String Driver = "org.gjt.mm.mysql.Driver";private static String url = "jdbc:mysql://127.0.0.1:3306/cmdb";private static String username = "root";private static String password = "root";//表头public static final String[] tableHeader = {"品牌","所属公司","分公司","店铺编码","渠道","业务渠道","店铺成本中心","店铺名称","POS店铺名称","上级","店铺间数","开店时间","关店时间","状态","开/关店原因","备注","装修面积","城市","城市等级"};//创建工作本public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();//创建表public static HSSFSheet demoSheet = demoWorkBook.createSheet("Sheet1");//表头的单元格个数目  public static final short cellNumber = (short)tableHeader.length;//数据库表的列数 public static final int columNumber = tableHeader.length;/** * 获得数据库连接 * @return conn */public static Connection getConn(){  try {Class.forName(Driver);conn = DriverManager.getConnection(url,username,password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return conn; }/** * 查找结果集 * @return * @throws SQLException */ public ResultSet selectAllDataFromDB() throws SQLException{conn = getConn();Statement stmt = conn.createStatement();  ResultSet rs = stmt.executeQuery("select J_BRAND,J_COM,J_DQ,J_DEPOTID,f_hzfs,B_CHANNEL,F_COSTCENTER," +"j_realname,J_NAME,J_FORM,P_JS,F_BEGINDATE,F_ENDDATE," +"J_FREEZE,f_kgdyy,f_bz,P_AREA,G_CS,G_CZ from storeinfo"); return rs; }/** * 创建表头 * @return */public void createTableHeader(){//HSSFHeader header = demoSheet.getHeader();//header.setCenter("大区信息表");HSSFRow headerRow = demoSheet.createRow((short) 0);for(int i = 0;i < cellNumber;i++){HSSFCell headerCell = headerRow.createCell((short) i);headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);headerCell.setCellValue(tableHeader[i]);headerCell.setCellStyle(getTitleStyle());}}/** * 创建行 * @param cells * @param rowIndex */public void createTableRow(List<String> cells,short rowIndex){//创建第rowIndex行HSSFRow row = demoSheet.createRow((short) rowIndex);row.setHeight((short) (15.625*20));HSSFCellStyle style = getCellStyle();for(short i = 0;i < cells.size();i++){//创建第i个单元格HSSFCell cell = row.createCell((short) i);cell.setCellStyle(style);cell.setEncoding(HSSFCell.ENCODING_UTF_16);        cell.setCellValue(cells.get(i));}}/** * 创建整个Excel表 * @throws SQLException  * */public void createExcelSheeet() throws SQLException{createTableHeader();ResultSet rs = selectAllDataFromDB();int rowIndex = 1;while(rs.next()){List<String> list = new ArrayList<String>();for(int i = 1;i <= columNumber;i++){list.add(rs.getString(i));}createTableRow(list,(short)rowIndex);rowIndex++;}}/** * 导出表格 * @param sheet * @param os * @return  * @throws IOException */public void expord(OutputStream os) throws IOException{demoSheet.setGridsPrinted(true);        HSSFFooter footer = demoSheet.getFooter();        footer.setRight("Page " + HSSFFooter.page() + " of " +        HSSFFooter.numPages());        demoWorkBook.write(os);}/** * 设置头部样式 * @return HSSFCellStyle */public HSSFCellStyle getTitleStyle(){HSSFCellStyle style = demoWorkBook.createCellStyle();      HSSFFont font = demoWorkBook.createFont();        demoSheet.setColumnWidth((short)7, (short) 7000);demoSheet.setColumnWidth((short)8, (short) 7000);demoSheet.setColumnWidth((short)11, (short) 5000);demoSheet.setColumnWidth((short)12, (short) 5000);        font.setFontName("Arial");      font.setFontHeightInPoints((short) 10);// 设置字体大小          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        style.setFillForegroundColor(HSSFColor.LAVENDER.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);          style.setBorderRight(HSSFCellStyle.BORDER_THIN);          style.setBorderTop(HSSFCellStyle.BORDER_THIN);          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);          style.setWrapText(true);          style.setFont(font);          return style;  }/** * 设置表格格样式 * @return HSSFCellStyle */public HSSFCellStyle getCellStyle(){HSSFCellStyle style = demoWorkBook.createCellStyle();          HSSFFont font = demoWorkBook.createFont();          font.setFontHeightInPoints((short) 10);// 设置字体大小        style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);          style.setBorderRight(HSSFCellStyle.BORDER_THIN);          style.setBorderTop(HSSFCellStyle.BORDER_THIN);          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);         style.setFont(font);          return style;  }}

在rest类中调用ExportExcelUtil

package ebc.baseserver.rest;import java.io.OutputStream;import java.net.URLEncoder;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletContext;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.ws.rs.GET;import javax.ws.rs.HeaderParam;import javax.ws.rs.Path;import javax.ws.rs.Produces;import javax.ws.rs.core.Context;import javax.ws.rs.core.UriInfo;import ebc.base.common.ExportExcelUtil;import ebc.base.wrapper.ResponseWrapper;import ebc.base.wrapper.Wrapper;@Path("/base/exportexcel")public class ExportExcelResource {@ContextServletContext sc;@ContextUriInfo uriInfo;@ContextHttpServletRequest request;@ContextHttpServletResponse response;@GET@Produces("application/json")public Wrapper Export(@HeaderParam("Range") String range){long time = System.currentTimeMillis();ExportExcelUtil export = new ExportExcelUtil();OutputStream out = null;String filename = "店铺信息表.xsl";ResultSet rs = null;try{export.createTableHeader();filename = URLEncoder.encode(filename,"utf-8");//解决在IE中文件名乱码rs = export.selectAllDataFromDB();int rowIndex = 1;while(rs.next()){List<String> list = new ArrayList<String>();for(int i = 1;i <= ExportExcelUtil.columNumber;i++){list.add(rs.getString(i));}export.createTableRow(list,(short)rowIndex);rowIndex++;}response.setContentType("application/ms-excel");response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"),"ISO-8859-1"));out = response.getOutputStream();export.expord(out);ResponseWrapper result = new ResponseWrapper(this);result.setCode("msg.001");result.setType("success");System.out.println("成功导出"+rowIndex+"条数据到"+filename+",共用时"+(System.currentTimeMillis()-time)/1000.0+"秒");return result;}catch(Exception e){e.printStackTrace();return new ResponseWrapper(e);}finally{try {if(rs!=null){rs.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

最后备注下

excel导出后文件名乱码了。

解决办法：

           response.setContentType("application/ms-excel");
            String browser = request.getHeader("user-agent");
            if(browser.indexOf("Firefox")!=-1){
                response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"),"ISO-8859-1"));
            }else{
                filename = URLEncoder.encode(filename,"utf-8");
                response.setHeader("Content-disposition", "attachment;filename="+filename);
            }
            out = response.getOutputStream();