使用poi操作导出excel代码示例
来源:互联网 发布:php 一键生成工具 编辑:程序博客网 时间:2024/06/11 22:58
package codes;import java.io.IOException;import java.io.OutputStream;import java.sql.Timestamp;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/*** * 通过AJAX示例,导出EXCEL示例: * JAVA中采用HSSFWorkbook创建EXCEL表格,设置输出格式输出 */public class ExportExcel_POI extends HttpServlet { private static final long serialVersionUID = 1L; protected void service(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { String method = request.getParameter("method"); if("prestartExcel".equals(method)){ prestartExcel(request, response); }else if("downloadExcel".equals(method)){ downloadExcel(request, response); } } private void prestartExcel(HttpServletRequest request, HttpServletResponse response) { //产生的Excel文件的名称 //产生工作簿对象 HSSFWorkbook workbook = new HSSFWorkbook(); //产生工作表对象 HSSFSheet sheet = workbook.createSheet(); //创建生成EXCEL头部 List<String> lists = getExcelHeader(); createExcelHeader(workbook, sheet, lists);//sheet表、单元格样式、数据集合 //创建生成EXCEL实体数据 Map<String, List<Object>> maps = getExcelBody(); createExcelBody(workbook, sheet, maps);//sheet表、body的数据集合 HttpSession session = request.getSession(); session.setAttribute("excel", workbook); } private void createExcelHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<String> lists) { //设置第一个工作表的名称为firstSheet workbook.setSheetName(0,"自动化设备检修"); sheet.setDefaultColumnWidth(15);//设置默认每一列的宽度 //sheet.setDefaultRowHeight((short) (20 * 20));//设置默认每一行的行高 //产生第二个工作表对象 HSSFSheet sheet2 = workbook.createSheet(); //设置第二个工作表的名称为secondSheet workbook.setSheetName(1,"自动化设备检修2"); //设置单元格样式:可以对字体、前景色、背景色等设置,参考POI的API HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体 cellStyle.setFont(font); HSSFRow row = sheet.createRow(0); HSSFCell cell = null; for(int i = 0;i < lists.size();i++){ cell = row.createCell(i); cell.setCellValue(lists.get(i)); cell.setCellStyle(cellStyle); } } private void createExcelBody(HSSFWorkbook workbook, HSSFSheet sheet, Map<String, List<Object>> maps) { //定义单元格样式 HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFDataFormat dataFormat = workbook.createDataFormat(); cellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd hh:mm"));//时间格式 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);//对齐方式 HSSFRow row; HSSFCell cell; int rows = maps.keySet().size(); List<Object> bodylist = null; int index = 0; for(int i = 0;i < rows;i++){ bodylist = maps.get(String.valueOf(i)); if(bodylist != null){ row = sheet.createRow(++index); for(int j = 0;j < bodylist.size();j++){ cell = row.createCell(j); Object data = bodylist.get(j) == null? "" : bodylist.get(j); if(data != null && "java.sql.Timestamp".equals(data.getClass().getName())){//判断如果为时间格式则: Date date = new Date(((Timestamp)data).getTime()); cell.setCellValue(date); cell.setCellStyle(cellStyle); }else{//如果为null、string则: cell.setCellValue(data.toString()); } } } } } /*** * 从SESSION中取出EXCEL * @param request * @param response * @throws IOException */ private void downloadExcel(HttpServletRequest request, HttpServletResponse response) throws IOException{ String filename = "自动化系统设备检修流程"; HttpSession session = request.getSession(); HSSFWorkbook workbook = (HSSFWorkbook)session.getAttribute("excel"); OutputStream out = response.getOutputStream();// 取得输出流 response.reset();// 清空输出流 response.setCharacterEncoding("UTF-8"); //设置返回的头字段:http协议inline采用浏览器方式打开、attachment采用本地EXCEL方式打开 response.setHeader("Content-disposition", "inline;filename = " + java.net.URLEncoder.encode(filename, "UTF-8") + ".xls"); //response.setHeader("Content-disposition", "attachment;filename = " + java.net.URLEncoder.encode(filename, "UTF-8") + ".xls"); response.setContentType("application/vnd.ms-excel;charset=UTF-8");//定义输出类型 workbook.write(out); out.flush(); out.close(); } /*** * 准备导出EXCEL的头部数据 * @return */ private List<String> getExcelHeader(){ List<String> lists = new ArrayList<String>(); lists.add("申请单号");lists.add("单位名称"); lists.add("设备名称");lists.add("所属系统名称"); lists.add("电压等级");lists.add("紧急程度"); lists.add("维护内容");lists.add("处理日期"); return lists; } /*** * 准备导出EXCEL的实体数据 * @return */ private Map<String, List<Object>> getExcelBody(){ Map<String, List<Object>> maps = new HashMap<String, List<Object>>(); Date date = new Date();//模拟日期数据 Timestamp timestamp = Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date)); Object[][] tabledate = { {"ZDH_001","变电站1","110kV设备","系统1","220kV","一般","nr_1",timestamp}, {"ZDH_002","变电站2","111kV设备","系统2","330kV","紧急","nr_2",timestamp}, {"ZDH_003","变电站3","112kV设备","系统3","440kV","紧急","nr_3",timestamp}, {"ZDH_004","变电站4","113kV设备","系统4","550kV","一般","nr_4",timestamp}, {"ZDH_006","变电站5","114kV设备","系统5","660kV","紧急","nr_5",timestamp}, {"ZDH_007","变电站6","115kV设备","系统6","770kV","一般","nr_6",timestamp}, {"ZDH_008","变电站7","116kV设备","系统7","880kV","紧急","nr_7",null}, {"ZDH_009","变电站8","117kV设备","系统8","990kV","一般","nr_8",timestamp} }; Object[] contents = null; List<Object> lists = null; for(int i = 0;i < tabledate.length;i++){//循环行数 contents = tabledate[i]; lists = new ArrayList<Object>(); for(int j = 0;j < contents.length;j++){//循环列数 lists.add(contents[j]); } System.out.print("index: " + i + " "); maps.put(String.valueOf(i), lists);//存放:行号、该行记录内容 } System.out.println(); return maps; }}
参考:http://blog.csdn.net/cunxiyuan108/article/details/8168211
阅读全文
0 0
- 使用poi操作导出excel代码示例
- 使用poi操作导出excel代码示例
- 使用poi操作导入excel代码示例
- 使用poi操作导入excel代码示例
- 使用POI导出Excel示例
- poi导出excel后台代码示例
- Java导出Excel文档使用poi操作
- poi操作导出excel、
- poi导出excel操作
- POI操作Excel示例
- poi导出excel代码
- 用POI实现excel文件导出 代码示例分享
- Struts2 POI导出Excel 基本思路解析及相关示例代码
- 使用poi导出excel
- 使用POI导出Excel
- 使用POI导出excel
- 使用POI导出Excel
- 使用POI导出excel
- 【笔记】Nexus思维导图
- hdu 1171 背包
- (转载)gcc -l参数和-L参数
- vb.net 教程 12-5 webbrowser 与窗体交互1
- java swing 确定进度条
- 使用poi操作导出excel代码示例
- 常用shell(2):shell监控进程的cpu,内存占用(定时采集)
- 给初学者的RxJava2.0教程(九)
- Java项目转maven项目,java项目转web项目
- 2017年8月15日 星期二
- <jQuery>Basic
- HDU
- 深入浅出数据仓库中SQL性能优化之Hive篇
- 从零开始做rpg游戏 (-) 写在前面的话