/** * Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件] * * @param sheetName 写入excel的sheet名称 * @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件 * @param map 要导出的字段。(格式:字段名 <-> 字段中文名)其中字段名应该是对应javaBean中的某属性。<br> * 若该字段为公式的话,必须符合一定的格式:公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符(,:)&操作符<br> * 如:SUM&A&C&2&,&- 它返回的公司是:SUM(A3,-C3)。具体参照: <code>com.integral.util.office.ExcelFormula * </code> * @param dateFormat 日期格式(可选),默认:yyyy-MM-dd * @author:[代超] * @throws IOException * @throws Exception * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ @SuppressWarnings("deprecation") public void writExcelFile( String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat) throws Exception { if (dataSet == null || dataSet.size() < 1) { return; } if (map == null || map.size() < 1) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } sheetName = sheetName == null ? "" : sheetName; // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 设置表格的样式 CellStyle headerStyle = setRootSheetSysle(workbook); // 产生表格标题行 HSSFRow row = sheet.createRow(0); // 将第一行冻结 sheet.createFreezePane(1, 1); if (map != null) { Object[] obj = map.values().toArray(); for (int i = 0; i < obj.length; i++) { HSSFCell cell = row.createCell(i); String header = obj[i] == null ? "" : obj[i].toString(); cell.setCellStyle(headerStyle); if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) { // 公式 header = obj[i].toString().replace("formula", ""); } HSSFRichTextString text = new HSSFRichTextString(header); cell.setCellValue(text); } } // 遍历数据集合,产生数据行 Iterator<T> it = dataSet.iterator(); for (int i = 1; it.hasNext(); i++) { row = sheet.createRow(i); T t = it.next(); // BeanMap bm = new BeanMap(t); Map b = PropertyUtils.describe(t); Map c = new TreeMap(); // 排序 c.putAll(b); writeRow(row, c, map, dateFormat, t); } workbook.write(out); }
/** * 生成文件 * * @throws IOException */ public byte[] run() throws IOException { // 初始化参数 initParameter(); // 实例化decorator initDecorator(); // 初始化 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度 try { wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16); } catch (Exception ex) { logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况"); } baseCellStyle = wb.createCellStyle(); baseCellStyle.setAlignment(align); baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat)); // 创建标题行 HSSFRow row = sheet.createRow((short) 0); // 创建标题 for (int i = 0; i < nameList.size(); i++) { HSSFCell cell = row.createCell((short) i); writeCell(cell, (String) nameList.get(i)); } // 创建数据 for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow((short) i + 1); for (int j = 0; j < fieldList.size(); j++) { HSSFCell cell = row.createCell((short) j); Object value = getValue(dataList.get(i), (String) fieldList.get(j)); writeCell(cell, value); } } // 写入文件 // FileOutputStream fileOut = new FileOutputStream(fileName); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); return out.toByteArray(); // fileOut.close(); }
public static OutputStream creatExcel( String SheetName, String excelName, HttpServletResponse response, List<String> HeadName, String[][] data, List<Integer> colunmWidth) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(SheetName); CellStyle s = new CellStyle(); Font f12 = new Font(); HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12); HSSFCellStyle style = ExcelUtil.getNewStyles(workbook, s, font12); sheet.setDefaultColumnWidth(50); sheet.setDefaultRowHeight((short) 400); response.setContentType("application/ms-excel;"); response.setHeader( "Content-Disposition", "attachment;Filename=" + ExcelUtil.toUtf8String(excelName)); sheet.setColumnWidth(0, (HeadName.size()) * 256); OutputStream os = response.getOutputStream(); // 设置excel的表头 for (int i = 0; i < HeadName.size(); i++) { ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style); } // 设置excel数据 for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style); } } // 设置excel 表格的列宽 for (int i = 0; i < HeadName.size(); i++) { sheet.setColumnWidth(i, colunmWidth.get(i)); } workbook.write(os); return os; }
/* * (non-Javadoc) * * @seeorg.springframework.web.servlet.view.document.AbstractExcelView# * buildExcelDocument(java.util.Map, * org.apache.poi.hssf.usermodel.HSSFWorkbook, * javax.servlet.http.HttpServletRequest, * javax.servlet.http.HttpServletResponse) */ @Override protected void buildExcelDocument( Map model, HSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception { HSSFSheet sheet; HSSFRow sheetRow; HSSFCell cell; // Go to the first sheet // getSheetAt: only if wb is created from an existing document // sheet = wb.getSheetAt(0); sheet = wb.createSheet("Spring"); sheet.setDefaultColumnWidth((short) 12); // write a text at A1 cell = getCell(sheet, 0, 0); setText(cell, "Spring-Excel test"); List words = (List) model.get("wordList"); for (int i = 0; i < words.size(); i++) { cell = getCell(sheet, 2 + i, 0); setText(cell, (String) words.get(i)); } }
public void buildExcelDocument( Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = workbook.createSheet("list"); sheet.setDefaultColumnWidth((short) 12); HSSFCell cell = getCell(sheet, 0, 0); setText(cell, "会员记录导出"); cell = getCell(sheet, 0, 1); setText(cell, "序号"); cell = getCell(sheet, 0, 2); setText(cell, "姓名"); cell = getCell(sheet, 0, 3); // setText(cell, "性别"); // cell = getCell(sheet, 0, 4); // setText(cell, "电话"); // cell = getCell(sheet, 0, 5); // setText(cell, "地址"); // cell = getCell(sheet, 0, 6); // setText(cell, "电子邮件"); // cell = getCell(sheet, 0, 7); // setText(cell, "证件类型"); // cell = getCell(sheet, 0, 8); // setText(cell, "证件号码"); // cell = getCell(sheet, 0, 9); // setText(cell, "是否接受推送"); int lie = 0, hang = 0; HSSFCellStyle dateStyle = workbook.createCellStyle(); // dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy")); // List<Member> list=(List<Member>) model.get("list"); // for(int i=0;i<list.size();i++){ // cell = getCell(sheet, i+1, 1); // setText(cell, list.get(i).getId().toString()); // cell = getCell(sheet, i+1, 2); // setText(cell, list.get(i).getName()); //// cell = getCell(sheet, i+1, 3); //// setText(cell, list.get(i).getSex()); //// cell = getCell(sheet, i+1, 4); //// setText(cell, list.get(i).getPhone()); //// cell = getCell(sheet, i+1, 5); //// setText(cell, list.get(i).getAddress()); //// cell = getCell(sheet, i+1, 6); //// setText(cell, list.get(i).getEmail()); //// cell = getCell(sheet, i+1, 7); //// setText(cell, list.get(i).getIdtype()); //// cell = getCell(sheet, i+1, 8); //// setText(cell, list.get(i).getIdnum()); //// cell = getCell(sheet, i+1, 9); //// setText(cell, list.get(i).getPushmsg()); // } SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); // 设置日期格式 System.out.println(df.format(new Date())); // new Date()为获取当前系统时间 String filename = "yonghuxiangqing" + df.format(new Date()) + ".xls"; response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }
/** * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param headers 表格属性列名数组 * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) */ @SuppressWarnings("unchecked") public Workbook exportExcel(String[] headers, Collection<T> dataset) { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth); // 生成一个样式 // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new HSSFRichTextString(docPrse)); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor(author); // 产生表格标题行 HSSFRow row = sheet.createRow(startRow); int colNum1 = startCol; for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(colNum1); if (useStyle) cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); colNum1++; } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = startRow; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); int colRow2 = startCol; for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(colRow2); if (useStyle) cell.setCellStyle(style2); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Boolean) { boolean bValue = (Boolean) value; textValue = "男"; if (!bValue) { textValue = "女"; } } else if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); textValue = sdf.format(date); } else if (value instanceof byte[]) { // 有图片时,设置行高为60px; row.setHeightInPoints(picCellHeight); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, picCellWidth); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index); anchor.setAnchorType(2); patriarch.createPicture( anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } colRow2++; } } return workbook; }
/** * 导出 * * @param exportData 导出数据 * @param rowMapper 导出列 * @param outPutPath 文件存放路径 * @param filename 导出文件名 * @return */ public static File exportExcel( List exportData, Map columnMapper, String outPutPath, String filename) { OutputStream out = null; File xlsFile = new File(outPutPath + filename + ".xls"); File parent = xlsFile.getParentFile(); if (parent != null && !parent.exists()) { parent.mkdirs(); } try { xlsFile.createNewFile(); out = new FileOutputStream(xlsFile); } catch (IOException e1) { e1.printStackTrace(); } LinkedHashMap rowMapper = new LinkedHashMap(); for (Iterator propertyIterator = columnMapper.entrySet().iterator(); propertyIterator.hasNext(); ) { java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next(); rowMapper.put(propertyEntry.getKey(), propertyEntry.getValue()); } String[] headers = MapToArrayUtils.mapToArrayByValue(rowMapper); // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(filename); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据,产生数据行 Iterator it = exportData.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); HSSFCell cell = null; Object rowObject = it.next(); String[] fields = MapToArrayUtils.mapToArrayByKey(rowMapper); for (int i = 0; i < fields.length; i++) { Object val = null; try { val = PropertyUtils.getProperty(rowObject, fields[i]); cell = row.createCell((short) i); cell.setCellStyle(style2); String value = null; if (val instanceof Date) { value = (val == null ? null : DateUtil.DateToString((Date) val, DateStyle.YYYY_MM_DD_HH_MM_SS.getValue())); } else { value = (val == null ? null : val.toString()); } if (BeanUtils.getProperty(rowObject, fields[i]) != null) { Pattern pattern = Pattern.compile("^-?\\d+(\\.\\d{1,5})?$"); Matcher matcher = pattern.matcher(value); if (matcher.find()) { value = value + "\t"; // 避免数字变成科学计数法,前后加\t,或前面加= cell.setCellValue(value); } else { HSSFRichTextString richString = new HSSFRichTextString(value); cell.setCellValue(richString); } } } catch (Exception e) { e.printStackTrace(); } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } finally { // 关闭资源 if (null != out) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } return xlsFile; }
public void exportJgjExcel( String title, List<String[]> header, List<Object[]> dataset, OutputStream out, short[] width, List<int[]> merge) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { row = sheet.createRow(i); for (int m = 0; m < header.get(i).length; m++) { HSSFCell cell = row.createCell(m); cell.setCellStyle(style); String headValue = header.get(i)[m]; // HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]); cell.setCellValue(headValue); } } if (merge != null && merge.size() > 0) { for (int m = 0; m < merge.size(); m++) { sheet.addMergedRegion( new CellRangeAddress( merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行 } } // sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); // sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + header.size()); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void exportExcel( String headline, String title, String[] headers, List<Object[]> dataset, OutputStream out, short[] width) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); if (row != null) { HSSFCell cell = row.createCell(0); HSSFFont f = workbook.createFont(); f.setColor(HSSFColor.DARK_BLUE.index); f.setFontHeightInPoints((short) 24); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 HSSFCellStyle s = workbook.createCellStyle(); s.setAlignment(HSSFCellStyle.ALIGN_CENTER); s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); s.setFont(f); cell.setCellStyle(s); cell.setCellValue(headline); } sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1))); row = sheet.createRow(1); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + 2); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }