ExcelUtil.java 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116
  1. package com.yihu.hos.standard.util;
  2. import org.apache.poi.hssf.usermodel.*;
  3. import org.apache.poi.ss.usermodel.IndexedColors;
  4. import javax.servlet.http.HttpServletResponse;
  5. import java.io.File;
  6. import java.io.FileOutputStream;
  7. import java.io.OutputStream;
  8. import java.util.List;
  9. import java.util.UUID;
  10. /**
  11. * Created by Administrator on 2016/6/24.
  12. */
  13. public class ExcelUtil {
  14. public static void downFileByHttpResponse(HttpServletResponse response, List<ExcelModel> excelModels) throws Exception {
  15. response.setHeader("Content-disposition", "attachment; filename= adapt.xlsx");// 设定输出文件头
  16. response.setContentType("application/x-msdownload");// 定义输出类型
  17. OutputStream os = response.getOutputStream();// 取得输出流
  18. HSSFWorkbook xssfWorkbook = new HSSFWorkbook();
  19. HSSFCellStyle style=xssfWorkbook.createCellStyle();
  20. for (ExcelModel excelModel : excelModels) {
  21. HSSFSheet sheet = xssfWorkbook.createSheet(excelModel.getSheetName());
  22. //设置表头
  23. HSSFRow row = sheet.createRow(0);
  24. for (int i = 0; i < excelModel.getHead().size(); i++) {
  25. sheet.setColumnWidth(i, 5000);
  26. HSSFCell cell= row.createCell(i);
  27. style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//設置背景色
  28. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  29. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
  30. HSSFFont font = xssfWorkbook.createFont();
  31. font.setFontName("黑体");
  32. font.setFontHeightInPoints((short) 12);
  33. style.setFont(font);
  34. cell.setCellStyle(style);
  35. cell.setCellValue(excelModel.getHead().get(i));
  36. }
  37. for (int i = 0; i <excelModel.getContext().size(); i++) {
  38. List<String> rowData = excelModel.getContext().get(i);
  39. row = sheet.createRow(i + 1);
  40. for (int j = 0; j < rowData.size(); j++) {
  41. HSSFCell cell= row.createCell(j);
  42. cell.setCellValue(rowData.get(j));
  43. }
  44. }
  45. }
  46. //主体内容生成结束
  47. xssfWorkbook.write(os); // 写入文件
  48. xssfWorkbook.close();
  49. os.flush();
  50. os.close();
  51. }
  52. public static File saveAsFile(List<ExcelModel> excelModels,String path) throws Exception {
  53. File newPath=new File(path);
  54. newPath.mkdir();
  55. File file=new File(path+ UUID.randomUUID().toString().replaceAll("-", "")+".xls");
  56. OutputStream outputStream=new FileOutputStream(file);
  57. HSSFWorkbook xssfWorkbook = new HSSFWorkbook();
  58. HSSFCellStyle style=xssfWorkbook.createCellStyle();
  59. for (ExcelModel excelModel : excelModels) {
  60. HSSFSheet sheet = xssfWorkbook.createSheet(excelModel.getSheetName());
  61. //设置表头
  62. HSSFRow row = sheet.createRow(0);
  63. for (int i = 0; i < excelModel.getHead().size(); i++) {
  64. sheet.setColumnWidth(i, 5000);
  65. HSSFCell cell= row.createCell(i);
  66. style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//設置背景色
  67. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  68. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
  69. HSSFFont font = xssfWorkbook.createFont();
  70. font.setFontName("黑体");
  71. font.setFontHeightInPoints((short) 12);
  72. style.setFont(font);
  73. cell.setCellStyle(style);
  74. cell.setCellValue(excelModel.getHead().get(i));
  75. }
  76. for (int i = 0; i <excelModel.getContext().size(); i++) {
  77. List<String> rowData = excelModel.getContext().get(i);
  78. row = sheet.createRow(i + 1);
  79. for (int j = 0; j < rowData.size(); j++) {
  80. HSSFCell cell= row.createCell(j);
  81. cell.setCellValue(rowData.get(j));
  82. }
  83. }
  84. }
  85. //主体内容生成结束
  86. xssfWorkbook.write(outputStream); // 写入文件
  87. xssfWorkbook.close();
  88. outputStream.flush();
  89. outputStream.close();
  90. return file;
  91. }
  92. }