java导出ecxel

最新推荐文章于 2022-11-21 13:43:14 发布

原创最新推荐文章于 2022-11-21 13:43:14 发布 · 303 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #导出excel

java知识专栏收录该内容

55 篇文章

订阅专栏

@Override
public void exportFile(String catalogIds,HttpServletResponse response) {
 String fileName = "导出文件信息列表.xls";
 try {
 this.setResponseHeader(response, fileName);
 OutputStream os = response.getOutputStream();
 ExportExcelUtil eeu = new ExportExcelUtil();
 HSSFWorkbook workbook = new HSSFWorkbook();
 List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
 // 截取;之前的字符串
 String fileIds = catalogIds.substring(0, catalogIds.indexOf(";"));
 if(!fileIds.equals("") && fileIds != null) {// 判断是否选择了文件选择的话进行打包压缩下载否则不执行
  String[] fileIdsArray = fileIds.split(",");
  for(int j = 0; j < fileIdsArray.length; j++) {
   List<Map<String, Object>> map=catalogMapper.exportFiles(fileIdsArray[j]);
   resultList.addAll(map);
  }

 }

 // 截取;之后的字符串
 String muluIds = catalogIds.substring(fileIds.length() + 1, catalogIds.length());
 if(!muluIds.equals("") && muluIds != null) {
  String [] muluIdsArray = muluIds.split(",");
  for(int i = 0; i < muluIdsArray.length; i++) {
   List<Map<String, Object>> map=catalogMapper.exportFolders(muluIdsArray[i]);
   resultList.addAll(map);
  }

 }
 if (Objects.nonNull(resultList)&&resultList.size() > 0) {
  String a="文件名,文件路径,大小,上传人,上传时间";
  String b="catalogName,catalogPath,fileSize,uploadUser,uploadTime";
  String[] headers =a.split(",");
  String[] columns = b.split(",");
  String[][] content = new String[resultList.size()][headers.length];
  for (int i = 0; i < resultList.size(); i++) {
   content[i] = new String[headers.length];
   for (int j = 0; j <columns.length;j++ ) {
    content[i][j] = Objects.isNull(resultList.get(i).get(columns[j]))?null:resultList.get(i).get(columns[j]).toString();
   }
  }

  eeu.exportExcel(workbook, 0,"文件信息", headers, content, os);
 }
  workbook.write(os);
  os.flush();
  os.close();
 } catch (IOException e) {
  e.printStackTrace();
 } catch (Exception e) {
  e.printStackTrace();
 }

}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
 try {
  response.setContentType("application/octet-stream");
  String file_name = URLEncoder.encode(fileName, "UTF-8");
  response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
  response.setHeader("Content-disposition", "attachment;filename=" + file_name);
 } catch (Exception ex) {
  ex.printStackTrace();
 }
}



import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

/**
 * ExportExcelUtil<br>
 *
 * @author lijianjun <br>
 * @time 2021/12/6 16:23 <br>
 */
public class ExportExcelUtil {

    /**
     *
     * @param title 标题
     * @param headers  表头
     * @param values  表中元素
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], String[][] values){

        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);
        //创建标题合并行
        hssfSheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short)headers.length - 1));

        //设置标题样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);   //设置居中样式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置标题字体
        Font titleFont = hssfWorkbook.createFont();
        titleFont.setFontHeightInPoints((short) 28);
        style.setFont(titleFont);

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);   //设置居中样式
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);
        Font titleFont0 = hssfWorkbook.createFont();
        titleFont0.setFontHeightInPoints((short) 20);
        hssfCellStyle.setFont(titleFont0);

        //设置表内容样式
        //创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style1 = hssfWorkbook.createCellStyle();
        style1.setAlignment(HorizontalAlignment.RIGHT);
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);
        Font titleFont1 = hssfWorkbook.createFont();
        titleFont1.setFontHeightInPoints((short) 16);
        style1.setFont(titleFont1);
        style1.setWrapText(true);

        //产生标题行
        HSSFRow hssfRow = hssfSheet.createRow(0);
        HSSFCell cell = hssfRow.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(style);



        //产生表头
        HSSFRow row1 = hssfSheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell hssfCell = row1.createCell(i);
            hssfCell.setCellValue(headers[i]);
            hssfCell.setCellStyle(hssfCellStyle);
        }

        //创建内容
        for (int i = 0; i <values.length; i++){
            row1 = hssfSheet.createRow(i +2);
            for (int j = 0; j < values[i].length; j++){
                //将内容按顺序赋给对应列对象
                HSSFCell hssfCell = row1.createCell(j);
                hssfCell.setCellValue(values[i][j]);
                hssfCell.setCellStyle(style1);
                hssfSheet.setColumnWidth(i,values[i][j].getBytes().length*256+4000>15000?15000:values[i][j].getBytes().length*256+4000);
            }
            row1.setHeightInPoints(20);

        }
        return hssfWorkbook;
    }


    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     * @param sheetTitle  （sheet的名称）
     * @param headers    （表格的标题）
     * @param values   （表格的数据）
     * @param out  （输出流）
     * @throws Exception
     */
    public Boolean exportExcel(HSSFWorkbook workbook, int sheetNum,
                               String sheetTitle, String[] headers, String[][] values,
                               OutputStream out) {
        Boolean res = false ;
        try{
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(sheetNum, sheetTitle);
            // 设置表格默认列宽度为20个字节
            sheet.setDefaultColumnWidth((short) 20);
            // 生成一个样式
            CellStyle style = workbook.createCellStyle();
            // 设置这些样式
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);


            HSSFCellStyle style1 = workbook.createCellStyle();
            style1.setAlignment(HorizontalAlignment.RIGHT);
            style1.setBorderBottom(BorderStyle.THIN);
            style1.setBorderLeft(BorderStyle.THIN);
            style1.setBorderRight(BorderStyle.THIN);
            style1.setBorderTop(BorderStyle.THIN);
            Font titleFont1 = workbook.createFont();
            titleFont1.setFontHeightInPoints((short) 14);
            style1.setFont(titleFont1);
            style1.setWrapText(true);


            // 生成一个字体
            Font headerFont = workbook.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 18);
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            // 把字体应用到当前的样式
            style.setFont(headerFont);

            // 指定当单元格内容显示不下时自动换行
            style.setWrapText(true);

            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell((short) i);

                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text.toString());
            }
            /*// 遍历集合数据，产生数据行
            if (result != null) {
                int index = 1;
                for (List<String> m : result) {
                    row = sheet.createRow(index);
                    int cellIndex = 0;
                    for (String str : m) {
                        HSSFCell cell = row.createCell((short) cellIndex);
                        cell.setCellValue(str.toString());
                        cellIndex++;
                    }
                    index++;
                }
            }*/

            //创建内容
            for (int i = 0; i <values.length; i++){
                row = sheet.createRow(i +1);
                for (int j = 0; j < values[i].length; j++){
                    //将内容按顺序赋给对应列对象
                    HSSFCell hssfCell = row.createCell(j);
                    hssfCell.setCellValue(values[i][j]);
                    hssfCell.setCellStyle(style1);
                    //sheet.setColumnWidth(i, Objects.isNull(values[i][j])?50:values[i][j].getBytes().length*256+4000>15000?15000:values[i][j].getBytes().length*256+4000);
                    //sheet.setColumnWidth(i,15000);
                }
                row.setHeightInPoints(20);

            }

            res = true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }


    public static SXSSFWorkbook createTemplate(String sheetName, int titleColumnNumber, String titleName, List<String> columnNameList) {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(18);
        CellStyle style = workbook.createCellStyle();
        if (titleColumnNumber > 1) {
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumnNumber));
        }

        SXSSFRow rowTitle = sheet.createRow(0);
        style.setAlignment(HorizontalAlignment.LEFT);
        SXSSFCell titleCell = rowTitle.createCell(0);
        titleCell.setCellValue(titleName);
        CellStyle titleStyle = getCellStyle(workbook);
        titleCell.setCellStyle(titleStyle);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short)20);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        if (CollectionUtils.isNotEmpty(columnNameList)) {
            CellStyle columnTitleStyle = getCellStyle(workbook);
            Font columnTitleFont = workbook.createFont();
            columnTitleFont.setFontHeightInPoints((short)12);
            columnTitleFont.setBold(true);
            columnTitleStyle.setFont(columnTitleFont);
            SXSSFRow row = sheet.createRow(1);

            for(int i = 0; i < columnNameList.size(); ++i) {
                SXSSFCell columnCell = row.createCell(i);
                columnCell.setCellValue((String)columnNameList.get(i));
                columnCell.setCellStyle(columnTitleStyle);
            }
        }

        return workbook;
    }

    private static CellStyle getCellStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }

    public static Workbook getWorkbook(String excelName) {
        Workbook workbook = null;
        ClassPathResource classPathResource = new ClassPathResource("excel/" + excelName);

        try {
            InputStream io = classPathResource.getInputStream();
            workbook = WorkbookFactory.create(io);
        } catch (IOException var4) {
            var4.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        return workbook;
    }
}