1.首先查询出数据库的数据转化成json格式
如:String totalJson = paymentDayRptService.queryPayDayRtpForExport();
service层中获取到totalJson。
String totalList;;
List<PaymentDayRpt> paymentDayRptList = paymentDayRptDao.queryPayDayRtpExport(startDay,
endDay, paymentPlatform, paymentPlatformSubType, serviceType, DM_SCHEMA);
for (PaymentDayRpt payDayRpt : paymentDayRptList) {
Object[] arrs = {payDayRpt.getDayId(),
swithPlatform(payDayRpt.getPaymentPlatform()),
swithSubType(payDayRpt.getPaymentPlatformSubType()),
swithServiceType(payDayRpt.getServiceType()),
payDayRpt.getWbCdrs(), payDayRpt.getWbwjFees(), payDayRpt.getNbtzCdrs(),
payDayRpt.getNbtzFees(), payDayRpt.getBclsCdrs(), payDayRpt.getBclsFees(),
payDayRpt.getCxlsCdrs(), payDayRpt.getCxlsFees(),
payDayRpt.getCylsCdrs(), payDayRpt.getCylsFees()};
totalList.add(arrs);
}
if (totalList != null && totalList.size() > 0) {
ObjectMapper mapper = new ObjectMapper();
totalJson = mapper.writeValueAsString(totalList);
}
2.然后将Stringjson转化成List<String>.
List<String[]> totalList = null;
if (totalJson != null) {
ObjectMapper mapper = new ObjectMapper();
totalList = mapper.readValue(totalJson,
new TypeReference<List<String[]>>() {
});
}
3.找到模板路劲。
String filePath = this.getClass().getClassLoader().getResource(File.separator).getPath()
+ "/template/export/payDayRpt.xlsx";
File file = new File(filePath);
4.响应处理-关于IE浏览器和非IE的处理。
String userAgent = request.getHeader("User-Agent");
// 针对IE或者以IE为内核的浏览器:
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(downFileName, "UTF-8"));
} else {
// 非IE浏览器的处理:
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(downFileName.getBytes("UTF-8"), "iso8859-1"));
}
5.将文件以流返回
InputStream is = new FileInputStream(filePath);
6.ExportUtils中的generateExcelByTemplate方法
参数一为eslx类型:一般都是"07"
参数二为从第几行开始:
参数三位totalList
参数四为文件流
参数五为响应信息
ExportUtils.generateExcelByTemplate("07", 3, totalList, is, response.getOutputStream());
7.ExportUtils的工具类
public static void generateExcelByTemplate(String type, int startRowNum,
List<String[]> dataList, InputStream is,
OutputStream os) throws IOException {
if ("07".equals(type)) {
SXSSFWorkbook wb = null;
try {
XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(is));
wb = new SXSSFWorkbook(workbook, 100);
} catch (InvalidFormatException var9) {
var9.printStackTrace();
}
if (wb != null) {
Font font = wb.createFont();
font.setFontHeightInPoints((short) 10);
font.setFontName("宋体");
Sheet sheet = wb.getSheetAt(0);
CellStyle style = wb.createCellStyle();
style.setFont(font);
writeXSSFExcel(startRowNum, sheet, style, dataList);
wb.write(os);
os.close();
wb.dispose();
}
}
if ("03".equals(type)) {
generateExcelByTemplate(startRowNum, dataList, is, os);
}
}
private static void writeXSSFExcel(int startRowNum, Sheet sheet,
CellStyle style, List<String[]> dataList) {
if (dataList != null && dataList.size() > 0) {
Row row = null;
Cell cell = null;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
sheet.setDefaultColumnWidth(20);
for (String[] temp : dataList) {
row = sheet.createRow(startRowNum - 1);
for (int i = 0; i < temp.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new XSSFRichTextString(temp[i]));
}
startRowNum++;
}
}
}
public static void generateExcelByTemplate(int startRowNum, List<String[]> dataList,
InputStream is, OutputStream os) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
Font font = wb.createFont();
font.setFontHeightInPoints((short) 11);
font.setFontName("宋体");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
writeExcel(startRowNum, sheet, style, dataList);
wb.write(os);
os.flush();
}
private static void writeExcel(int startRowNum, Sheet sheet, CellStyle style,
List<String[]> dataList) {
if (dataList != null && dataList.size() > 0) {
Row row = null;
Cell cell = null;
style.setAlignment((short) 2);
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
style.setBorderLeft((short) 1);
style.setBorderRight((short) 1);
sheet.setDefaultColumnWidth(20);
for (Iterator var6 = dataList.iterator(); var6.hasNext(); ++startRowNum) {
String[] temp = (String[]) var6.next();
row = sheet.createRow(startRowNum - 1);
for (int i = 0; i < temp.length; ++i) {
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellType(1);
cell.setCellValue(new HSSFRichTextString(temp[i]));
}
}
}
}
以上按步骤应该可以正常导出。
bug出现的可能就是,最后注意文件xlsx中,不能删除任何列,不然后少数据。