poi导出excel
来源:互联网 发布:九章算法 大数据 编辑:程序博客网 时间:2024/05/18 11:05
poi导出excel工具类
- 工具类
package com.huaxia.utils;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* @description 导出Excel文档工具
* @date : 2017年2月22日 上午9:30:39
*/
public class ExcelUtil {
/** * 创建excel文档, * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 * */public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet(页),并命名 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。 for(int i=0;i<keys.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式(用于列名) f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式(用于值) f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式(用于列名) cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式(用于值) cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for(int i=0;i<columnNames.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (short i = 1; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行,在页sheet上 Row row1 = sheet.createRow((short) i); // 在row行上创建一个方格 for(short j=0;j<keys.length;j++){ Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb;}/** * 下载Excel * @param workbook * @param fileName Excel 名称 * @param response */public static void downloadExcel(Workbook workbook,String fileName,HttpServletResponse response){ ByteArrayOutputStream os = new ByteArrayOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { workbook.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { e.printStackTrace(); } finally { if (bis != null) try { bis.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } if (bos != null) try { bos.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}
}
@Overridepublic List<Map<String, Object>> createExcelRecord(List<EnterStatement> list) { List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>(); Map<String, Object> map = new HashMap<String, Object>(); map.put("sheetName", "sheet1"); listmap.add(map); if(list != null && list.size() > 0){ SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for(EnterStatement obj : list){ Map<String, Object> mapValue = new HashMap<String, Object>(); mapValue.put("platOrderNo",obj.getPlatOrderNo() ); mapValue.put("operOrderNo", obj.getOperOrderNo()); mapValue.put("enterName", obj.getEnterName()); mapValue.put("enterCode", obj.getEnterCode()); mapValue.put("createTime", obj.getCreateTime() != null ? format.format(obj.getCreateTime()) : ""); mapValue.put("replyTime", obj.getReplyTime()!=null?format.format(obj.getReplyTime() ):"" ); mapValue.put("reqStatusStr",obj.getReqStatusStr() ); mapValue.put("phone", obj.getPhone()); mapValue.put("platServiceName", obj.getPlatServiceName()); mapValue.put("serviceTypeStr", obj.getServiceTypeStr()); mapValue.put("serviceMethodStr", obj.getServiceMethodStr()); mapValue.put("serviceMoneyStr", obj.getServiceMoneyStr()); mapValue.put("discountRateStr", obj.getDiscountRateStr()); mapValue.put("feeStr", obj.getFeeStr()); listmap.add(mapValue); } } return listmap;}
@RequestMapping(“/exportExcel”)
@SystemControllerLog(operatePoint=OperatePointConstants.EMP_CUSTOMER, operateType=OperateTypeConstants.EXPORT_STATEMENT)
public String exportExcel(HttpServletResponse response){
ReqData reqData = this.getReqData();
String fileName=”企业客户对账单”;
String columnNames[]={ "平台订单编号","企业订单编号","客户名称","客户编号", "订单创建时间","订单报竣时间","操作结果","用户手机号", "平台服务名称","服务类型","服务方式","服务金额", "折扣率","实际扣费金额" };//列名 String keys[] = { "platOrderNo","operOrderNo","enterName","enterCode", "createTime","replyTime","reqStatusStr","phone", "platServiceName","serviceTypeStr","serviceMethodStr","serviceMoneyStr", "discountRateStr","feeStr" };//map中的key List<EnterStatement> enterStatements =statementService.findStatementList(reqData); List<Map<String,Object>> list=statementService.createExcelRecord(enterStatements); Workbook workbook = ExcelUtil.createWorkBook(list,keys,columnNames); ExcelUtil.downloadExcel(workbook, fileName, response); return null;}
- Excel POI导出excel
- POI-----POI导出Excel实例
- jsp导出excel poi
- POI导出EXCEL
- poi导出Excel
- poi导出excel
- POI 导出excel
- POI 导出excel
- POI 实现导出Excel
- java poi 导出excel
- poi 方式导出excel
- POI 导出Excel
- 使用poi导出excel
- poi导出excel
- poi导出excel实例
- 使用POI导出Excel
- POI 导出EXCEL
- 导出excel poi
- Hello World · GitHub 指南
- css3 box-sizing属性
- iBET Live Casinos Rebate 0.75% Bonus
- XShell鼠标选中, 实现快捷复制和粘贴
- windows的磁盘操作之七——获取当前所有的物理磁盘号
- poi导出excel
- 算法-03 贪心和分治
- Linux下安装Apache服务
- android 突破DEX 方法数65535限制
- android(hello world)-1
- Android安全加密:对称加密
- C语言strtok()函数:字符串分割
- Swift -入门到跑路(1)
- windows的磁盘操作之八——格式化分区的思考