code sample

XlsxUtils.java (text/x-csrc), 16.24 KB, created by onlyarticle on 2016-09-19 08:34:51 UTC

(hide)

Description: code sample

Filename:

MIME Type:

Creator: onlyarticle

Created: 2016-09-19 08:34:51 UTC

Size: 16.24 KB

patch

obsolete

>/** > * @(#)XlsxFileUtils.java 2016年8月11日 > * > * Copyright 2000-2013 by ChinaNetCenter Corporation. > * > * All rights reserved. > * > * This software is the confidential and proprietary information of > * ChinaNetCenter Corporation ("Confidential Information"). You > * shall not disclose such Confidential Information and shall use > * it only in accordance with the terms of the license agreement > * you entered into with ChinaNetCenter. > * > */ > >package com.cnc.rmp.util; > >import java.awt.Color; >import java.io.ByteArrayInputStream; >import java.io.ByteArrayOutputStream; >import java.io.IOException; >import java.io.InputStream; >import java.io.OutputStream; >import java.text.SimpleDateFormat; >import java.util.Collection; >import java.util.Date; >import java.util.Iterator; >import java.util.List; >import java.util.Map; >import java.util.regex.Matcher; >import java.util.regex.Pattern; > >import org.apache.poi.hssf.usermodel.HSSFCellStyle; >import org.apache.poi.hssf.util.HSSFColor; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.xssf.usermodel.XSSFCell; >import org.apache.poi.xssf.usermodel.XSSFCellStyle; >import org.apache.poi.xssf.usermodel.XSSFColor; >import org.apache.poi.xssf.usermodel.XSSFFont; >import org.apache.poi.xssf.usermodel.XSSFRichTextString; >import org.apache.poi.xssf.usermodel.XSSFRow; >import org.apache.poi.xssf.usermodel.XSSFSheet; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; >import org.slf4j.Logger; >import org.slf4j.LoggerFactory; > >import com.cnc.portal.exception.GenericException; >import com.cnc.portal.util.CollectionUtil; >import com.cnc.portal.util.StringUtil; >import com.cnc.rmp.vo.entity.excel.ExcelTableEntity; > >/** > * xlsx 表格操作类. > * > * @author suyc@chinanetcenter.com > * @date 2016年8月11日 > * @version 1.0.0 > */ >public class XlsxUtils extends com.cnc.commons.utils.FileUtils { > > private final static Logger LOGGER = LoggerFactory > .getLogger(XlsxUtils.class); > > private static final int sheetMaxSize = 60000; > > public final static short SKY_BLUE = 0x28; > public final static short LIGHT_YELLOW = 0x2b; > public final static short WHITE = 0x9; > > private XlsxUtils() { > > } > > public static void buildExcelHeader(XSSFWorkbook workbook, XSSFSheet sheet, > XSSFCellStyle style, String[] headerName) { > XSSFRow row = sheet.createRow(0); > setCellStyle4Title(workbook, style); > for (int i = 0; i < headerName.length; i++) { > XSSFCell cell = row.createCell(i); > cell.setCellStyle(style); > XSSFRichTextString text = new XSSFRichTextString(headerName[i]); > cell.setCellValue(text); > } > } > > public static void buildExcelHeader(XSSFWorkbook workbook, XSSFSheet sheet, > XSSFCellStyle style, List<List<String>> headerss, > List<List<Integer>> indexss) { > > style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 > style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中 > setCellStyle4Title(workbook, style); > > for (int i = 0; i < headerss.size(); i++) { > XSSFRow row = sheet.createRow(i); > List<String> headers = headerss.get(i); > List<Integer> indexs = indexss.get(i); > for (int j = 0; j < headers.size(); j++) { > XSSFCell cell = row.createCell(indexs.get(j)); > cell.setCellStyle(style); > XSSFRichTextString text = new XSSFRichTextString(headers.get(j)); > cell.setCellValue(text); > } > } > } > > public static void buildExcelRow(XSSFWorkbook workbook, XSSFFont font, > XSSFSheet sheet, XSSFCellStyle style, int rowNum, String[] colkeys, > Map<String, Object> record) { > XSSFRow row = sheet.createRow(rowNum); > setCellStyle4Row(workbook, style, font); > String textValue = StringUtil.EMPTY; > String pattern = "yyyy-MM-dd"; > for (int i = 0; i < colkeys.length; i++) { > XSSFCell cell = row.createCell(i); > cell.setCellStyle(style); > Object value = record.get(colkeys[i]); > if (value instanceof Date) { > Date date = (Date) value; > SimpleDateFormat sdf = new SimpleDateFormat(pattern); > textValue = sdf.format(date); > } else { > textValue = StringUtil.toString(value); > } > // 利用正则表达式判断textValue是否全部由数字组成 > if (StringUtil.isNotEmpty(textValue)) { > Pattern p = Pattern.compile("^//d+(//.//d+)?$"); > Matcher matcher = p.matcher(textValue); > if (matcher.matches()) { > // 是数字当作double处理 > cell.setCellValue(Double.parseDouble(textValue)); > } else { > XSSFRichTextString richString = new XSSFRichTextString( > textValue); > richString.applyFont(font); > cell.setCellValue(richString); > } > > } > } > } > > public static InputStream exportXLS(ExcelTableEntity excel) > throws IOException { > if (CommonUtils.isNull(excel)) { > return null; > } > ByteArrayOutputStream out = new ByteArrayOutputStream(); > exportXLS(excel, out); > if ((out.toByteArray()) != null && (out.toByteArray().length > 0)) { > InputStream in = new ByteArrayInputStream(out.toByteArray()); > return in; > } > return null; > } > > public static InputStream exportXLS(List<ExcelTableEntity> excels) > throws IOException { > if (CollectionUtil.isEmpty(excels)) { > return null; > } > ByteArrayOutputStream out = new ByteArrayOutputStream(); > exportXLS(excels, out); > if ((out.toByteArray()) != null && (out.toByteArray().length > 0)) { > InputStream in = new ByteArrayInputStream(out.toByteArray()); > return in; > } > return null; > } > > public static void exportXLS(ExcelTableEntity excel, OutputStream out) > throws IOException { > try { > XSSFWorkbook workbook = new XSSFWorkbook(); > > int rowNum = 1; > int sheetNum = 1; > String title = excel.getTitle(); > // 生成一个表格 > XSSFSheet sheet = workbook.createSheet(title); > // 如果没有设置各列宽度，则默认为15，如果有，则设置各列宽度 > Map<Integer, Integer> map = excel.getWidthMap(); > if (map.isEmpty()) { > sheet.setDefaultColumnWidth(excel.getDefaultColumnWidth()); > } else { > if (excel.getColumns() != null) { > int length = excel.getColumns().length; > for (int i = 0; i < length; i++) { > sheet.setColumnWidth(i, > excel.getDefaultColumnWidth() * 256); > } > } > > for (Integer key : map.keySet()) { > sheet.setColumnWidth(key, map.get(key) * 256); > } > } > > String[] headerNames = excel.getHeaders(); > XSSFCellStyle style = workbook.createCellStyle();// XSSFCellStyle在外面定义，公用一个 > style.setWrapText(true); > buildExcelHeader(workbook, sheet, style, headerNames); > String[] colkeys = excel.getColumns(); > Collection<Map<String, Object>> rows = excel.getRows(); > if (rows != null && rows.size() > 0) { > XSSFFont font = workbook.createFont();// XSSFFont在外面定义，公用一个 > font.setColor(new XSSFColor(Color.BLACK)); > style = workbook.createCellStyle();// XSSFCellStyle在外面定义，公用一个 > for (Iterator<Map<String, Object>> it = rows.iterator(); it > .hasNext();) { > Map<String, Object> tmp = it.next(); > buildExcelRow(workbook, font, sheet, style, rowNum++, > colkeys, tmp); > if (rowNum > sheetMaxSize) { > rowNum = 1; > sheetNum++; > sheet = workbook.createSheet(title + " " + sheetNum); > // 设置表格默认列宽度为15个字节(非首个sheet) > sheet.setDefaultColumnWidth(excel > .getDefaultColumnWidth()); > style = workbook.createCellStyle(); > buildExcelHeader(workbook, sheet, style, headerNames); > } > } > } > workbook.write(out); > } catch (Exception e) { > if (LOGGER.isErrorEnabled()) { > LOGGER.error(e.getMessage(), e); > } > throw new GenericException("rmp.validator.error.report", > e.getCause()); > } > > } > > public static void exportXLS(List<ExcelTableEntity> excels, OutputStream out) > throws IOException { > try { > XSSFWorkbook workbook = new XSSFWorkbook(); > for (ExcelTableEntity excel : excels) { > int rowNum = 1; > if (CollectionUtil.isNotEmpty(excel.getHeaderss())) { > rowNum = excel.getHeaderss().size(); > } > int sheetNum = 1; > String title = excel.getTitle(); > // 生成一个表格 > XSSFSheet sheet = workbook.createSheet(title); > if (CollectionUtil.isNotEmpty(excel.getMergedRegionss())) { > mergedCell(sheet, excel.getMergedRegionss()); > } > > // 设置表格默认列宽度为15个字节 > sheet.setDefaultColumnWidth(excel.getDefaultColumnWidth()); > String[] headerNames = excel.getHeaders(); > XSSFCellStyle style = workbook.createCellStyle();// XSSFCellStyle在外面定义，公用一个 > if (CollectionUtil.isNotEmpty(excel.getHeaderss())) { > buildExcelHeader(workbook, sheet, style, > excel.getHeaderss(), excel.getIndexss()); > } else { > buildExcelHeader(workbook, sheet, style, headerNames); > } > String[] colkeys = excel.getColumns(); > Collection<Map<String, Object>> rows = excel.getRows(); > if (rows != null && rows.size() > 0) { > XSSFFont font = workbook.createFont();// XSSFFont在外面定义，公用一个 > font.setColor(new XSSFColor(Color.BLACK)); > style = workbook.createCellStyle();// XSSFCellStyle在外面定义，公用一个 > for (Iterator<Map<String, Object>> it = rows.iterator(); it > .hasNext();) { > Map<String, Object> tmp = it.next(); > buildExcelRow(workbook, font, sheet, style, rowNum++, > colkeys, tmp); > if (rowNum > sheetMaxSize) { > rowNum = 1; > sheetNum++; > sheet = workbook > .createSheet(title + " " + sheetNum); > // 设置表格默认列宽度为15个字节(非首个sheet) > sheet.setDefaultColumnWidth(excel > .getDefaultColumnWidth()); > style = workbook.createCellStyle(); > buildExcelHeader(workbook, sheet, style, > headerNames); > } > } > } > } > workbook.write(out); > } catch (Exception e) { > if (LOGGER.isErrorEnabled()) { > LOGGER.error(e.getMessage(), e); > } > throw new GenericException("rmp.validator.error.report", > e.getCause()); > } > > } > > //合并单元格 > private static void mergedCell(XSSFSheet sheet, > List<List<Integer>> mergedRegionss) { > for (int i = 0; i < mergedRegionss.size(); i++) { > List<Integer> mergedRegions = mergedRegionss.get(i); > sheet.addMergedRegion(new CellRangeAddress(mergedRegions.get(0), mergedRegions.get(1), mergedRegions.get(2), mergedRegions.get(3))); > } > } > > private static void setCellStyle4Title(XSSFWorkbook workbook, > XSSFCellStyle style) { > // 设置这些样式 > style.setFillForegroundColor(SKY_BLUE); > style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); > style.setBorderBottom(XSSFCellStyle.BORDER_THIN); > style.setBorderLeft(XSSFCellStyle.BORDER_THIN); > style.setBorderRight(XSSFCellStyle.BORDER_THIN); > style.setBorderTop(XSSFCellStyle.BORDER_THIN); > style.setAlignment(XSSFCellStyle.ALIGN_CENTER); > // 生成一个字体 > XSSFFont font = workbook.createFont(); > font.setColor(new XSSFColor(Color.BLACK)); > font.setFontHeightInPoints((short) 12); > font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); > // 把字体应用到当前的样式 > style.setFont(font); > } > > private static void setCellStyle4Row(XSSFWorkbook workbook, > XSSFCellStyle style, XSSFFont font) { > // 生成并设置另一个样式 > style.setFillForegroundColor(new XSSFColor(Color.WHITE)); > style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); > style.setBorderBottom(XSSFCellStyle.BORDER_THIN); > style.setBorderLeft(XSSFCellStyle.BORDER_THIN); > style.setBorderRight(XSSFCellStyle.BORDER_THIN); > style.setBorderTop(XSSFCellStyle.BORDER_THIN); > style.setAlignment(XSSFCellStyle.ALIGN_LEFT); > style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); > style.setWrapText(true); > // 把字体应用到当前的样式 > style.setFont(font); > } > > /** > * ****************以下为原来ExcelUtils 的内容******************************** > */ > public static void exportXLS(ExcelTableEntity excel, XSSFWorkbook workbook) > throws IOException { > SheetProcessor processor = null; > exportXLS(excel, workbook, processor); > } > > public static void exportXLS(ExcelTableEntity excel, XSSFWorkbook workbook, > SheetProcessor processor) throws IOException { > try { > int rowNum = 1; > int sheetNum = 1; > String title = excel.getTitle(); > // 生成一个表格 > XSSFSheet sheet = workbook.createSheet(title); > // 设置表格默认列宽度为15个字节 > sheet.setDefaultColumnWidth(15); > String[] headerNames = excel.getHeaders(); > XSSFCellStyle style = workbook.createCellStyle();// HSSFCellStyle在外面定义，公用一个 > XSSFCellStyle deviceStyle = workbook.createCellStyle();// HSSFCellStyle在外面定义，公用一个 > buildExcelHeader(workbook, sheet, style, headerNames); > String[] colkeys = excel.getColumns(); > Collection<Map<String, Object>> rows = excel.getRows(); > if (rows != null && rows.size() > 0) { > XSSFFont font = workbook.createFont();// HSSFFont在外面定义，公用一个 > font.setColor(HSSFColor.BLACK.index); > style = workbook.createCellStyle();// HSSFCellStyle在外面定义，公用一个 > for (Iterator<Map<String, Object>> it = rows.iterator(); it > .hasNext();) { > Map<String, Object> tmp = it.next(); > buildExcelRow(workbook, font, sheet, style, deviceStyle, > rowNum++, colkeys, tmp); > if (rowNum > 60000) { > sheet.createFreezePane(1, 0); > rowNum = 1; > sheetNum++; > sheet = workbook.createSheet(title + " " + sheetNum); > // 设置表格默认列宽度为15个字节(非首个sheet) > sheet.setDefaultColumnWidth(15); > style = workbook.createCellStyle(); > buildExcelHeader(workbook, sheet, style, headerNames); > } > } > // sheet.createFreezePane(1, 0); > > if (processor != null) { > processor.process(sheet); > } > } > // workbook.write(out); > } catch (Exception e) { > if (LOGGER.isErrorEnabled()) { > LOGGER.error(e.getMessage(), e); > } > throw new GenericException("rmp.validator.error.report", > e.getCause()); > } > > } > > private static void buildExcelRow(XSSFWorkbook workbook, XSSFFont font, > XSSFSheet sheet, XSSFCellStyle style, XSSFCellStyle deviceStyle, > int rowNum, String[] colkeys, Map<String, Object> record) { > XSSFRow row = sheet.createRow(rowNum); > boolean isNormalStyle = true; > if (!isNormalStyle) { > setCellStyle4Row(workbook, deviceStyle, font, isNormalStyle); > } else { > setCellStyle4Row(workbook, style, font, isNormalStyle); > } > String textValue = StringUtil.EMPTY; > String pattern = "yyyy-MM-dd"; > for (int i = 0; i < colkeys.length; i++) { > XSSFCell cell = row.createCell(i); > if (isNormalStyle) { > cell.setCellStyle(style); > } else { > cell.setCellStyle(deviceStyle); > } > Object value = record.get(colkeys[i]); > if (value instanceof Date) { > Date date = (Date) value; > SimpleDateFormat sdf = new SimpleDateFormat(pattern); > textValue = sdf.format(date); > } else { > textValue = StringUtil.toString(value); > } > // 利用正则表达式判断textValue是否全部由数字组成 > if (StringUtil.isNotEmpty(textValue)) { > Pattern p = Pattern.compile("^//d+(//.//d+)?$"); > Matcher matcher = p.matcher(textValue); > if (matcher.matches()) { > // 是数字当作double处理 > cell.setCellValue(Double.parseDouble(textValue)); > } else { > XSSFRichTextString richString = new XSSFRichTextString( > textValue); > richString.applyFont(font); > cell.setCellValue(richString); > } > > } > } > } > > private static void setCellStyle4Row(XSSFWorkbook workbook, > XSSFCellStyle style, XSSFFont font, Boolean isServer) { > // 生成并设置另一个样式 > if (!isServer) { > style.setFillForegroundColor(LIGHT_YELLOW); > } else { > style.setFillForegroundColor(WHITE); > } > 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_LEFT); > style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); > // 把字体应用到当前的样式 > style.setFont(font); > } > > public interface SheetProcessor { > public void process(XSSFSheet sheet); > } > >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]