excel 导出(模板、非模板(动态表头合并表格))与解析
开始
1:代码
@Override
public void getStatisticsDownloadInfo(String type, HttpServletResponse response,
HttpServletRequest request) throws IOException {
ExportMyWord emw = new ExportMyWord();
Map<String, Object> exlParam = new HashMap<>();
String templateName = "";
if ("模板".equals(type)) {
List<Map<String, Object>> resultList = new ArrayList<>();
exlParam.put("resultList", resultList);
templateName = "统计表.ftl";
} else {
dynamicTable(response, request);
return;
}
InputStream input = null;
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("统计.xls", "UTF-8"));
OutputStream outputStream = response.getOutputStream();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String filePath = File.separator +format.format(new Date())+".xls";
try {
emw.createWord(exlParam, templateName, filePath);
byte[] bytes = new byte[1024 * 50];
int len;
File file = new File(filePath);
input = new FileInputStream(file);
while ((len = input.read(bytes, 0, 1024)) != -1) {
outputStream.write(bytes, 0, len);
}
} finally {
if (input != null) {
input.close();
}
outputStream.close();
FileUtil.clearFiles(filePath);
}
}
private void dynamicTable(HttpServletResponse response, HttpServletRequest request) throws IOException {
HSSFWorkbook hb = new HSSFWorkbook();
List<String> headerList = new ArrayList<>();
List<String> headerListB = new ArrayList<>();
String sheetName = "";
String fileName = null;
List<Map<String, Object>> resultList = new ArrayList<>();
headerList.add("表头A");
headerList.add("表头B");
headerList.add("表头C");
resultList = new ArrayList<>();
sheetName = "统计表";
fileName = "统计表.xls";
ExcelUtil.createExcelDetail(sheetName, headerList, headerListB, hb);
int inetRowIndex = 3;
for (Map<String, Object> map : resultList) {
List<String> objects = new ArrayList<>();
List<Map.Entry<String, Object>> list = new ArrayList<>(map.entrySet());
hb = ExcelUtil.createExcelData(hb, objects, 0, inetRowIndex++, headerList.size());
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("filename", getFilename(fileName, request));
OutputStream outputStream = response.getOutputStream();
hb.write(outputStream);
outputStream.close();
}
private String getFilename(String filename, HttpServletRequest request) throws UnsupportedEncodingException {
String userAgent = request.getHeader("USER-AGENT");//获取浏览器版本
if (StringUtils.contains(userAgent, "MSIE")) {//IE浏览器
filename = URLEncoder.encode(filename, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {//google,火狐浏览器
filename = new String(filename.getBytes(), "ISO8859-1");
} else {
filename = URLEncoder.encode(filename, "UTF8");//其他浏览器
}
return filename;
}
工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.text.SimpleDateFormat;
import java.util.List;
/**
* @author xiaofeng
* @history 修订历史(历次修订内容、修订人、修订时间等)
**/
public class ExcelUtil {
public static void createExcelDetail(String sheetName, List<String> row_second,
List<String> row_third, HSSFWorkbook wb, StatisticsTableDto statisticsTableDto) {
HSSFSheet sheet = wb.createSheet(sheetName);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 13);
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
headerStyle.setLocked(true);
headerStyle.setWrapText(false);
headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
headerStyle.setBorderTop(BorderStyle.THIN);//上边框
headerStyle.setBorderRight(BorderStyle.THIN);//右边框
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 400);
Cell c00 = row.createCell(0);
c00.setCellValue(sheetName);
c00.setCellStyle(headerStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, row_second.size() - 1));
Row r2 = sheet.createRow(1);
r2.setHeight((short) 300);
for (int i = 0; i < row_second.size(); i++) {
Cell tempCell = r2.createCell(i);
sheet.setColumnWidth(i, Math.max(row_second.get(i).getBytes().length * 256, 12 * 256));
tempCell.setCellValue(row_second.get(i));
tempCell.setCellStyle(headerStyle);
}
sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, row_second.size() - 1));
Row r3 = sheet.createRow(2);
r3.setHeight((short) 300);
for (int i = 0; i < row_third.size(); i++) {
Cell tempCell = r3.createCell(i);
sheet.setColumnWidth(i, 12 * 256);
tempCell.setCellValue(row_third.get(i));
tempCell.setCellStyle(headerStyle);
}
}
public static HSSFWorkbook createExcelData(HSSFWorkbook excel, List<String> excelData, int sheetAt, int rowIndex, int columnSum) {
HSSFRow row = excel.getSheetAt(sheetAt).createRow(rowIndex);
Font headerFont = excel.createFont();
headerFont.setFontHeightInPoints((short) 11);
CellStyle cellStyle = excel.createCellStyle();
cellStyle.setFont(headerFont);
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
cellStyle.setLocked(true);
cellStyle.setWrapText(false);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
for (int i = 0; i < columnSum; i++) {
excel.getSheetAt(sheetAt).setColumnWidth(0, 3766);
if (i >= excelData.size()) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue("");
} else {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(excelData.get(i));
}
}
return excel;
}
}
老样子 解析见下篇 : 点我