/**
* @param request
* @param response
* @param map
* @return
* @throws IOException
*/
public static String newExportXls(HttpServletRequest request, HttpServletResponse response,
Map<String, Object> map) {
try {
logger.error("开始导出excel...");
// 文件名
String excelName = map.get("excelName").toString();
// sheet页的名字
String sheetName = map.get("sheetName").toString();
// 属性栏
ArrayList<String> totleList = (ArrayList<String>) map.get("totleList");
// 所有数据
ArrayList<ArrayList<String>> dataList = (ArrayList<ArrayList<String>>) map.get("dataList");
// 将list集合中的数据写到一个Excel文件中
HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件,当前这个文件在内存中
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 25);// 设置字体大小
HSSFSheet sheet = workbook.createSheet(sheetName);// 创建一个sheet页
HSSFRow headRow = sheet.createRow(0);// 创建标题行
// sheet.autoSizeColumn(i); // 设置列宽
style.setFont(font);
for (int i = 0; i < totleList.size(); i++) {
headRow.createCell(i).setCellValue(totleList.get(i));// 设置标题行的每个属性
sheet.setColumnWidth(i, 20 * 256); // 设置列宽
}
HSSFCellStyle contextstyle = workbook.createCellStyle();
for (int i = 0; i < dataList.size(); i++) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
int rowIndex = i + 1;
HSSFRow contentRow = sheet.createRow(rowIndex);
ArrayList<String> arrayList = dataList.get(i);
for (int j = 0; j < arrayList.size(); j++) {
// dataRow.createCell(j).setCellValue(arrayList.get(j));
HSSFCell contentCell = contentRow.createCell(j);
Boolean isInteger = false;// arrayList.get(j)是否为整数
// arrayList.get(j)(小数部分是否为0)
HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
if (arrayList.get(j) == null || "".equals(arrayList.get(j))) {
contentCell.setCellStyle(contextstyle);
// 设置单元格内容为字符型
contentCell.setCellValue(arrayList.get(j));
} else {
if (NumberUtils.isNumber(arrayList.get(j))) {
//判断字符串中是否全为数字
if(NumberUtils.isDigits(arrayList.get(j))){
contentCell.setCellStyle(contextstyle);
// 设置单元格内容为字符型
contentCell.setCellValue(arrayList.get(j));
}else{
//小数
contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));
contentCell.setCellStyle(contextstyle);
// 设置单元格内容为字符型
contentCell.setCellValue(Double.parseDouble(arrayList.get(j)));
}
} else {
contentCell.setCellStyle(contextstyle);
// 设置单元格内容为字符型
contentCell.setCellValue(arrayList.get(j));
}
}
}
}
// 文件下载:一个流(输出流)、两个头
ServletOutputStream out = response.getOutputStream();
String filename = excelName + ".xls";
filename = encodeDownloadFilename(filename, request.getHeader("user-agent"));
// String mimeType =
// request.getSession().getServletContext().getMimeType(filename);
// response.setContentType(mimeType);
response.setContentType("text/html;charset=utf-8");
response.setHeader("content-disposition", "attachment;filename=" + filename);
workbook.write(out);
out.close();
logger.info("导出excel完成.....");
} catch (Exception e) {
logger.error("导出excel失败,error=", e);
}
return null;
}
/**
* @Description: TODO(测试方法随时可以删除)
* @param request
* @param response
* @param noSettleList
* @return
* @throws IOException
*/
public static String test(HttpServletRequest request, HttpServletResponse response,
List<NoSettleInfoDTO> noSettleList) throws IOException {
Map<String, Object> map = new HashMap<>();
map.put("excelName", "商家列表");
map.put("sheetName", "第一批商家");
List<String> totleList = new ArrayList<>();
totleList.add("渠道");
totleList.add("门店");
totleList.add("商家名称");
totleList.add("订单号/退单号");
totleList.add("支付流水号");
totleList.add("支付方式");
totleList.add("发生时间");
totleList.add("订单金额");
totleList.add("手续费");
totleList.add("金额");
totleList.add("对方");
map.put("totleList", totleList);
List<List<String>> dataList = new ArrayList<>();
for (NoSettleInfoDTO noSettleInfo : noSettleList) {
List<String> list = new ArrayList<>();
list.add(noSettleInfo.getChannel());
list.add(noSettleInfo.getMarketName());
list.add(noSettleInfo.getSellerName());
list.add(noSettleInfo.getTransactionNo());
list.add(noSettleInfo.getTradeSerial());
list.add(noSettleInfo.getPayway());
list.add(noSettleInfo.getOrderTime());
list.add(noSettleInfo.getOrderAmount());
list.add(noSettleInfo.getPoundage());
list.add(noSettleInfo.getAmount());
list.add(noSettleInfo.getMobilephone());
dataList.add(list);
}
map.put("dataList", dataList);
newExportXls(request, response, map);
return null;
}
以Excel文件形式导出
最新推荐文章于 2024-09-02 14:29:34 发布