java导出Excel(非模板)可导出多个sheet方式

目录

• Java导出Excel(非模板)可导出多个sheet
• 方法
• 注意事项
• 总结

java导出Excel(非模板)可导出多个sheet

当初为了写导出也是废了不少劲，今天突然想起来就将此方法上传，之前也写过模板导出所以这个是非模板的。

方法

• 1.导出excel（单标题，单sheet）
• 2.含多个sheet 每个sheet中的标题一致
• 3.含多个sheet 每个sheet中的标题不一致
• 并且还有两个其他导出格式。

注意事项

将PageData换成Map即可！！！

 <!--依赖--> 
<dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.0.1</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
python      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooXML</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId> org.apache.httpcomponents </groupId>
      <artifactId> httpclient </artifactId>
      <version>4.5.9</version>
    </dependency>
   <!--json-lib -->
    <dependency>
      <groupId>net.sf.json-lib</groupId>
      <artifactId>json-lib</artifactId>
      <version>2.4</version>
      <classifier>jdk15</classifier>
    </dependency>
    <dependency>
      <groupId>net.sf.jxls</groupId>
      <artifactId>jxls-core</artifactId>
      <version>1.0-RC-1</version>
    </dependency>

 
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*js;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
 
/**
 * @Auther: sjj
 * @Date: 2019/5/29 16:50
 * @ClassName: ExcelUtil
 * @Description: 导出excel 
 */
public class ExcelUtil {
 
 
    /**
     * 导出excel
     * @param pds 需要导出的数据
     * @param pd 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头），fileName(文件名，只须写上名字，这里会进行处理拼接)
     * @param request
     * @param response
     * @return
     */
    public static void  exportData(List<LinkedHashMap<Object,Object>> pds, PageData pd, HttpServletRequest request, HttpServletResponse response) {
        if(pds.size()<1){
//            return StatusCode.IS_NULL;
            return;
        }
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //设置标题
            HSSFSheet sheet = workbook.createSheet(pd.getString("title"));
            //headers表示excel表中第一行的表头
            String[] headers=pd.getString("headers").split(",");
            //表头字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setColor(HSSFFont.COLOR_NORMAL);
            headerFont.setBold(true);
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle styleMain = workbook.createCellStyle();
            //水平居中
            styleMain.setAlignment(HorizontalAlignment.CENTER);
            styleMain.setFont(headerFont);
            //导出样式
            HSSFCellStyle cellStyle_C = workbook.createCellStyle();
            // 自动换行
            cellStyle_C.setWrapText(true);
            cellStyle_C.setAlignment(HorizontalAlignment.CENTER);
            //创建标题行
            HSSFRow row = sheet.createRow(0);
            //在excel表中添加表头
            for(int i=0;i<headers.length;i++){
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
                cell.setCellStyle(styleMain);
            }
 
            int rowNum = 1;
            //遍历集合数据
            for(LinkedHashMap<Object,Object> p  :pds){
                //依次创建行
                HSSFRow row1 = sheet.createRow(rowNum);
                int i=0;
                //遍历数据
                for (Object key: p.keySet()){
                    //创建行中列 并放入数据
                    HSSFCell cell = row1.createCell(i);
                    String value=String.valueOf(p.get(key));
                    cell.setCellValue(value!=null?value:"");
                    if("null".equals(value)){
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(cellStyle_C);
                    i++;
                }
                row1.setHeight((short) (26*10));
                rowNum++;
 
            }
            //自动调整列宽
            for (int i = 0;i <headers.length;i++){
                if(i==headers.length-2){
                    sheet.setColumnWidth(i, 20 * 256);
                    break;
                }
                if(i==3){
                    sheet.setColumnWidth(i, 20 * 256);
                    continue;
                }
                sheet.autoSizeColumn(i, true);
            }
            //取得输出流
            OutputStream out = response.getOutputStream();
 
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
            //清空缓存
            response.reset();
            //设置相应内容的编码格式
            response.setCharacterEncoding("UTF-8");
            //1.导出名称
            String fileName =pd.getString("fileName")+dateFormat.format(new Date());
            //文件名乱码
            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            //IE浏览器和Edge浏览器
            if (agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO")>0 && agent.indexOf("RV:11")>0)) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {  //其他浏览器
                fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            //定义输出类型
            response.setContentType("application/msexcel");
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
//            return StatusCode.ERROR;
        }
//        return StatusCode.SUCCESS;
    }
 
 
 
 
    /**
     * 导出excel  含多个sheet 每个sheet中的标题一致
     * @param pds 需要导出的数据
     * @param titles 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头）
     * @param fileName (文件名，只须写上名字，这里会进行处理拼接)
     * @param request
     * @param response
     * 注意 titles里面的数据顺序要与pds中一致  不然导出时会出现标题与内容不符
     * @return
     */
    public static void exportSheetData(Map<String,List<List<LinkedHashMap<Object,Object>>>> pds, List<PageData> titles,
                                       String fileName,HttpServletRequest request, HttpServletResponse response) throws IOException {
 
        if(pds.size()<1){
//            return StatusCode.IS_NULL;
            return;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
 
        //取得输出流
        OutputStream out = response.getOutputStream();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
        //重命名
        fileName+=dateFormat.format(new Date());;
        //清空缓存
        response.reset();
        //设置相应内容的编码格式
        response.setCharacterEncoding("UTF-8");
        //文件名乱码
        //获得浏览器信息并转换为大写
        String agent = request.getHeader("User-Agent").toUpperCase();
        //IE浏览器和Edge浏览器
        if (agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO")>0 && agent.indexOf("RV:11")>0)) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {  //其他浏览器
            fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        //定义输出类型
        response.setContentType("application/msexcel");
        int sheetNum=0;
        for(String nameKey:pds.keySet()){
            List<List<LinkedHashMap<Object,Object>>> data=pds.get(nameKey);
            //设置标题
            HSSFSheet sheet = workbook.createSheet();
            //工地/消纳场名作为sheet名
            workbook.setSheetName(sheetNum,nameKey);
            //表头字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setColor(HSSFFont.COLOR_NORMAL);
            headerFont.setBold(true);
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle styleMain = workbook.createCellStyle();
            //水平居中
            styleMain.setAlignment(HorizontalAlignment.CENTER);
            styleMain.setFont(headerFont);
            //导出样式
            HSSFCellStyle cellStyle_C = workbook.createCellStyle();
            // 自动换行
            cellStyle_C.setWrapText(true);
            cellStyle_C.setAlignment(HorizontalAlignment.CENTER);
 
            int titleNum=0;
            int rowNum = 1;
            int startRow=0;
            for(List<LinkedHashMap<Object,Object>> list:data){
                //放入数据
                //创建标题行
                //主标题
                HSSFRow title = null;
                //行标题
                HSSFRow row = null;
                //为了避免标题对内容进行覆盖
                if(http://www.devze.comrowNum==1){
                    title=sheet.createRow(0);
                    row = sheet.createRow(rowNum);
                    rowNum++;
                }else{
                    //让标题与上一行内容存在两个空行
       android             startRow=rowNum+2;
                    title=sheet.createRow(startRow);
                    row = sheet.createRow(rowNum+3);
                    rowNum+=4;
                }
                PageData pd=titles.get(titleNum);
                //headers表示excel表中第一行的表头
                String[] headers=pd.getString("headers").split(",");
                //创建主标题
                HSSFCell tieleCell = title.createCell(0);
                tieleCell.setCellValue(pd.getString("title"));
                tieleCell.setCellStyle(styleMain);
                for(int i=1;i<=headers.length;i++){
                    HSSFCell cell = title.createCell(i);
//                    HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                    cell.setCellValue("");
                    cell.setCellStyle(styleMain);
                }
                // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
                // 行和列都是从0开始计数，且起始结束都会合并
                // 这里是合并excel中日期的两行为一行
                CellRangeAddress region = new CellRangeAddress(startRow, startRow, 0, headers.length-1);
                sheet.addMergedRegion(region);
                //主标题创建结束
 
                //在excel表中添加表头
                for(int i=0;i<headers.length;i++){
                    HSSFCell cell = row.createCell(i);
                    HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                    cell.setCellValue(text);
                    cell.setCellStyle(styleMain);
                }
                //表头添加结束
 
                //遍历集合数据
                for(LinkedHashMap<Object,Object> p  :list){
                    //依次创建行
                    HSSFRow row1 = sheet.createRow(rowNum);
                    int i=0;
                    //遍历数据
                    for (Object key: p.keySet()){
                        //创建行中列 并放入数据
                        HSSFCell cell = row1.createCell(i);
                        String value=String.valueOf(p.get(key));
                        cell.setCellValue(value!=null?value:"");
                        if("null".equals(value)){
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle_C);
                        i++;
                    }
                    row1.setHeight((short) (26*10));
                    rowNum++;
 
                }
                //自动调整列宽
                for (int i = 0;i <headers.length;i++){
                    if(i==headers.length-2){
                        sheet.setColumnWidth(i, 20 * 256);
                        break;
                    }
                    if(i==3){
                        sheet.setColumnWidth(i, 20 * 256);
                        continue;
                    }
                    sheet.autoSizeColumn(i, true);
                }
                titleNum++;
//                }
            }
 
            sheetNum++;
 
        }
        //保存Excel文件
        workbook.write(out);
        //关闭文件流
        out.close();
 
    }
 
 
 
 
    /**
     * 导出excel  含多个sheet 每个sheet中的标题不一致
     * @param pds 需要导出的数据
     * @param titles 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头）,可以List<PageData>不一致
     * @param fileName (文件名，只须写上名字，这里会进行处理拼接)
     * @param request
     * @param response
     * 注意 titles里面的数据顺序要与pds中一致  不然导出时会出现标题与内容不符
     * @return
     */
    public static void exportSheetTitle(LinkedHashMap<String,List<List<LinkedHashMap<Object,Object>>>> pds, LinkedList<List<PageData>> titles,
                                       String fileName,HttpServjsletRequest request, HttpServletResponse response) throws IOException {
 
        if(pds.size()<1){
//            return StatusCode.IS_NULL;
            return;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
 
        //取得输出流
        OutputStream out = response.getOutputStream();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
        //重命名
        fileName+=dateFormat.format(new Date());;
        //清空缓存
        response.reset();
        //设置相应内容的编码格式
        response.setCharacterEncoding("UTF-8");
        //文件名乱码
        //获得浏览器信息并转换为大写
        String agent = request.getHeader("User-Agent").toUpperCase();
        //IE浏览器和Edge浏览器
        if (agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO")>0 && agent.indexOf("RV:11")>0)) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {  //其他浏览器
            fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        //定义输出类型
        response.setContentType("application/msexcel");
        int sheetNum=0;
        for(String nameKey:pds.keySet()){
            List<List<LinkedHashMap<Object,Object>>> data=pds.get(nameKey);
            //设置标题
            HSSFSheet sheet = workbook.createSheet();
            //工地/消纳场名作为sheet名
            workbook.setSheetName(sheetNum,nameKey);
            //表头字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setColor(HSSFFont.COLOR_NORMAL);
            headerFont.setBold(true);
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle styleMain = workbook.createCellStyle();
            //水平居中
            styleMain.setAlignment(HorizontalAlignment.CENTER);
            styleMain.setFont(headerFont);
            //导出样式
            HSSFCellStyle cellStyle_C = workbook.createCellStyle();
            // 自动换行
            cellStyle_C.setWrapText(true);
            cellStyle_C.setAlignment(HorizontalAlignment.CENTER);
 
            int titleNum=0;
            int rowNum = 1;
            int startRow=0;
            for(List<LinkedHashMap<Object,Object>> list:data){
                //放入数据
                //创建标题行
                //主标题
                HSSFRow title = null;
                //行标题
                HSSFRow row = null;
                //为了避免标题对内容进行覆盖
                if(rowNum==1){
                    title=sheet.createRow(0);
                    row = sheet.createRow(rowNum);
                    rowNum++;
                }else{
                    //让标题与上一行内容存在两个空行
                    startRow=rowNum+2;
                    title=sheet.createRow(startRow);
                    row = sheet.createRow(rowNum+3);
                    rowNum+=4;
                }
 
                //遍历每个sheet中的标题
                for(PageData pd:titles.get(sheetNum)){
                    //headers表示excel表中第一行的表头
                    String[] headers=pd.getString("headers").split(",");
                    //创建主标题
                    HSSFCell tieleCell = title.createCell(0);
                    tieleCell.setCellValue(pd.getString("title"));
                    tieleCell.setCellStyle(styleMain);
                    for(int i=1;i<=headers.length;i++){
                        HSSFCell cell = title.createCell(i);
//                    HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                        cell.setCellValue("");
                        cell.setCellStyle(styleMain);
                    }
                    // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
                    // 行和列都是从0开始计数，且起始结束都会合并
                    // 这里是合并excel中日期的两行为一行
                    CellRangeAddress region = new CellRangeAddress(startRow, startRow, 0, headers.length-1);
                    sheet.addMergedRegion(region);
                    //主标题创建结束
 
                    //在excel表中添加表头
                    for(int i=0;i<headers.length;i++){
                        HSSFCell cell = row.createCell(i);
                        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                        cell.setCellStyle(styleMain);
                    }
                    //表头添加结束
 
                    //遍历集合数据
                    for(LinkedHashMap<Object,Object> p  :list){
                        //依次创建行
                        HSSFRow row1 = sheet.createRow(rowNum);
                        int i=0;
                        //遍历数据
                        for (Object key: p.keySet()){
                            //创建行中列 并放入数据
                            HSSFCell cell = row1.createCell(i);
                            String value=String.valueOf(p.get(key));
                            cell.setCellValue(value!=null?value:"");
                            if("null".equals(value)){
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle_C);
                            i++;
                        }
                        row1.setHeight((short) (26*10));
                        rowNum++;
 
                    }
                    //自动调整列宽
                    for (int i = 0;i <headers.length;i++){
                        if(i==headers.length-2){
                            sheet.setColumnWidth(i, 20 * 256);
                            break;
                        }
                        if(i==3){
                            sheet.setColumnWidth(i, 20 * 256);
                            continue;
                        }
                        sheet.autoSizeColumn(i, true);
                    }
                }
 
                titleNum++;
//                }
            }
 
            sheetNum++;
 
        }
        //保存Excel文件
        workbook.write(out);
        //关闭文件流
        out.close();
 
    }
 
    /**
     * 导出excel
     * @qkp
     *
     * @param pds 需要导出的数据
     * @param pd 导出excel公用数据，需包含templateFile（模板文件），fileName(文件名，只须写上名字，这里会进行处理拼接)
     * @param mergeCells 合并单元格数组【firstRow,lastRow,firstCol,lastCol】
     * @param request
     * @param response
     * @return
     */
    public static void  exportData(Map<Object,Object> pds, PageData pd,int []mergeCells, HttpServletRequest request, HttpServletResponse response) {
 
        if(pds.size()<1){
            return;
        }
        try {
            XLSTransformer transformer = new XLSTransformer();
            InputStream in = new FileInputStream(new File(pd.getString("templateFile")));
 
            XSSFWorkbook workbook =(XSSFWorkbook)transformer.transformXLS(in, pds);
            Sheet sheet = workbook.getSheetAt(0);
            //合并单元格需要的逻辑
            sheet.addMergedRegion(new CellRangeAddress(mergeCells[0], mergeCells[1], mergeCells[2], mergeCells[3]));//合并单元格的函数
 
            //表头字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setColor(HSSFFont.COLOR_NORMAL);
            headerFont.setBold(true);
            //创建单元格，并设置值表头 设置表头居中
 
            //导出样式
            XSSFCellStyle cellStyle_C = workbook.createCellStyle();
            // 自动换行
            cellStyle_C.setWrapText(true);
            cellStyle_C.setAlignment(HorizontalAlignment.CENTER);
 
            //取得输出流
            OutputStream out = response.getOutputStream();
 
            //清空缓存
            response.reset();
            //设置相应内容的编码格式
            response.setCharacterEncoding("UTF-8");
            //1.导出名称
            String fileName =pd.getString("fileName");
            //文件名乱码
            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            //IE浏览器和Edge浏览器
            if (agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO")>0 && agent.indexOf("RV:11")>0)) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {  //其他浏览器
                fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            //定义输出类型
            //response.setContentType("application/msexcel");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
 
    }
 
    /**
     * 导出excel
     * @author qkp
     * @param pds 需要导出的数据
     * @param pd 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头），fileName(文件名，只须写上名字，这里会进行处理拼接)
     * @param request
     * @param response
     * @return
     */
    public static void  exportData2(List<LinkedHashMap<Object,Object>> pds, PageData pd, HttpServletRequest request, HttpServletResponse response) {
        if(pds.size()<1){
            return;
        }
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //设置标题
            HSSFSheet sheet = workbook.createSheet();
            //headers表示excel表中第一行的表头
            String[] headers=pd.getString("headers").split(",");
            //表头字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setColor(HSSFFont.COLOR_NORMAL);
            headerFont.setBold(true);
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle styleMain = workbook.createCellStyle();
            //水平居中
            styleMain.setAlignment(HorizontalAlignment.CENTER);
            styleMain.setFont(headerFont);
            styleMain.setBorderBottom(BorderStyle.THIN);
            styleMain.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            styleMain.setBorderTop(BorderStyle.THIN);
            styleMain.setTopBorderColor(IndexedColors.BLACK.getIndex());
            styleMain.setBorderLeft(BorderStyle.THIN);
            styleMain.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            styleMain.setBorderRight(BorderStyle.THIN);
            styleMain.setRightBorderColor(IndexedColors.BLACK.getIndex());
            //导出样式
            HSSFCellStyle cellStyle_C = workbook.createCellStyle();
            // 自动换行
            cellStyle_C.setWrapText(true);
            cellStyle_C.setAlignment(HorizontalAlignment.CENTER);
 
            cellStyle_C.setBorderBottom(BorderStyle.THIN);
            cellStyle_C.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle_C.setBorderTop(BorderStyle.THIN);
            cellStyle_C.setTopBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle_C.setBorderLeft(BorderStyle.THIN);
            cellStyle_C.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle_C.setBorderRight(BorderStyle.THIN);
            cellStyle_C.setRightBorderColor(IndexedColors.BLACK.getIndex());
 
 
            //创建列标题行
            HSSFRow row = sheet.createRow(0);
            //标题行样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            //表头字体
            Font titleFont = workbook.createFont();
            titleFont.setFontName("微软雅黑");
            titleFont.setColor(HSSFFont.COLOR_NORMAL);
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short)18);
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFCell titleCell =row.createCell(0);
            titleCell.setCellValue(pd.getString("title"));
            titleCell.setCellStyle(titleStyle);
            //合并单元格需要的逻辑
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,headers.length-1 ));//合并单元格的函数
            row.setRowStyle(titleStyle);
            row.setHeightInPoints(56);
            //列表题
            row = sheet.createRow(1);
            row.setHeightInPoints(39);
            //在excel表中添加表头
            for(int i=0;i<headers.length;i++){
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
                cell.setCellStyle(styleMain);
            }
 
            int rowNum = 2;
            //遍历集合数据
            for(LinkedHashMap<Object,Object> p  :pds){
                //依次创建行
                HSSFRow row1 = sheet.createRow(rowNum);
                int i=0;
                //遍历数据
                for (Object key: p.keySet()){
                    //创建行中列 并放入数据
                    HSSFCell cell = row1.createCell(i);
                    String value=String.valueOf(p.get(key));
                    cell.setCellValue(value!=null?value:"");
                    if("null".equals(value)){
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(cellStyle_C);
                    i++;
                }
                //row1.setHeight((short) (26*10));
                rowNum++;
 
            }
            //自动调整列宽
            for (int i = 0;i <headers.length;i++){
                if(i==headers.length-2){
                    sheet.setColumnWidth(i, 20 * 256);
                    break;
                }
                if(i==3){
                    sheet.setColumnWidth(i, 20 * 256);
                    continue;
                }
                sheet.autoSizeColumn(i);
                //sheet.autoSizeColumn(i, true);
            }
            //取得输出流
            OutputStream out = response.getOutputStream();
 
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
            //清空缓存
            response.reset();
            //设置相应内容的编码格式
            response.setCharacterEncoding("UTF-8");
            //1.导出名称
            String fileName =pd.getString("fileName")+dateFormat.format(new Date());
            //文件名乱码
            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            //IE浏览器和Edge浏览器
            if (agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO")>0 && agent.indexOf("RV:11")>0)) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {  //其他浏览器
                fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            //定义输出类型
            response.setContentType("application/msexcel");
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
//            return StatusCode.ERROR;
        }
//        return StatusCode.SUCCESS;
    }
 
}

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。