Java Excel POI
来源：互联网 发布：淘宝天猫店要多少钱 编辑：程序博客网 时间：2024/05/23 23:09
http://www.cnblogs.com/sshoub/p/4085115.html
1、使用
String toFileName = "E:\\sheet1.xlsx"; String fromFileName = "E:\\sheet2.xlsx"; MemoryToExcel memoryToExcel = new MemoryToExcel(); memoryToExcel.mergeExcel(toFileName, 1, fromFileName, 0); // String fileName = "E:\\test.xlsx"; // String sheetName = "Test"; // // Map<Integer, FrameworkEnums.EnumDBDataType> dataTypeByColumn = // new HashMap<Integer, EnumDBDataType>(); // dataTypeByColumn.put(1, EnumDBDataType.Int); // dataTypeByColumn.put(2, EnumDBDataType.Int); // dataTypeByColumn.put(3, EnumDBDataType.Date); // dataTypeByColumn.put(4, EnumDBDataType.DateTime); // // Map<Integer, String> dataFormatByColumn = new HashMap<Integer, // String>(); // //dataFormatByColumn.put(2, "0.00%"); // dataFormatByColumn.put(3, "yyyy-mm-dd"); // dataFormatByColumn.put(4, "yyyy-mm-dd hh:mm:ss"); // // Object[][] data = new Object[100][10]; // // for (int i = 0; i < 100; i++) { // for (int j = 0; j < 10; j++) { // if (j == 1) { // data[i][j] = i; // } else if (j == 2) { // data[i][j] = i + 0.6; // } else if (j == 3) { // data[i][j] = new Date(); // } else if (j == 4) { // data[i][j] = new Date(); // } else { // data[i][j] = "" + i + "+" + j; // } // } // } // // MemoryToExcel memoryToExcel = new MemoryToExcel(); // memoryToExcel.setFileName(fileName); // memoryToExcel.setSheetName(sheetName); // memoryToExcel.setTopSkipRows(2); // memoryToExcel.setSourceData(data); // memoryToExcel.setDataTypeByColumn(dataTypeByColumn); // memoryToExcel.setDataFormatByColumn(dataFormatByColumn); // memoryToExcel.start(); // System.out.println("写数据结束！"); // // memoryToExcel.setWrapCellData(0, 0, 0, "报表测试"); // for (int i = 0; i < 10; i++) { // memoryToExcel.setWrapCellData(0, 1, i, "累计\r\n外呼量" + i); // } // System.out.println("设置标题和列名结束！"); // memoryToExcel.setColumnWidth(0, 0, 9, 10); // memoryToExcel.setRowHeight(0, 1, 2, (short)15); // memoryToExcel.setAutoFit(0, 0, 2); // System.out.println("设置自适应结束！"); // Color color = new Color(54, 201, 201); // memoryToExcel.setBackgroundColor(0, 1, 1, 0, 10, color); // System.out.println("设置背景色结束！"); // // memoryToExcel.setMerge(0, 0, 0, 0, 9); // System.out.println("设置单元格合并结束！"); // // Font font = new Font("宋体", Font.BOLD, 12); // color = new Color(255, 0, 0); // memoryToExcel.setFont(0, 0, 1, 0, 1, font, color); // font = new Font("宋体", Font.BOLD, 10); // memoryToExcel.setFont(0, 1, 1, 0, 10, font); // font = new Font("宋体", Font.PLAIN, 9); // memoryToExcel.setFont(0, 2, 100, 0, 10, font); // System.out.println("设置字体结束！"); // // memoryToExcel.setBorder(0, 0, 100 + 2, 0, 10); // System.out.println("设置Border结束！"); // // memoryToExcel.setAlignment(0, 0, 100 + 2, 0, 10, // (short) 2, (short) 1); // System.out.println("设置对齐方式结束！"); // System.out.println("导出成功"); } catch (Exception e) { System.out.println(String.format("导出失败，%s", e.getMessage())); } System.exit(0);
2、核心代码
package nankang.test;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.Map;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;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.XSSFWorkbook;public class MemoryToExcel { private String m_FileName; private String m_SheetName; private int m_TopSkipRows = 0; private int m_TopSkipCols = 0; private int m_DataType2SartRow = 0; private Map<Integer, FrameworkEnums.EnumDBDataType> m_DataTypeByColumn = new HashMap<Integer, FrameworkEnums.EnumDBDataType>(); private Map<Integer, String> m_DataFormatByColumn = new HashMap<Integer, String>(); private Map<Integer, FrameworkEnums.EnumDBDataType> m_DataTypeByColumn2 = new HashMap<Integer, FrameworkEnums.EnumDBDataType>(); private Map<Integer, String> m_DataFormatByColumn2 = new HashMap<Integer, String>(); private Object[][] m_SourceData; /** * 返回 文件名称 * * @return 文件名称 */ public String getFileName() { return m_FileName; } /** * 设置文件名 * * @param fileName * 文件名称 */ public void setFileName(String fileName) { m_FileName = fileName; } public String getSheetName() { return m_SheetName; } public void setSheetName(String sheetName) { m_SheetName = sheetName; } public int getTopSkipRows() { return m_TopSkipRows; } public void setTopSkipRows(int topSkipRows) { m_TopSkipRows = topSkipRows; } public int getTopSkipCols() { return m_TopSkipCols; } public void setTopSkipCols(int topSkipCols) { m_TopSkipCols = topSkipCols; } public int getDataType2SartRow() { return m_DataType2SartRow; } public void setDataType2SartRow(int mDataType2SartRow) { m_DataType2SartRow = mDataType2SartRow; } public Map<Integer, FrameworkEnums.EnumDBDataType> getDataTypeByColumn() { return m_DataTypeByColumn; } public void setDataTypeByColumn( Map<Integer, FrameworkEnums.EnumDBDataType> dataTypeByColumn) { m_DataTypeByColumn = dataTypeByColumn; } public Map<Integer, String> getDataFormatByColumn() { return m_DataFormatByColumn; } public void setDataFormatByColumn(Map<Integer, String> dataFormatByColumn) { m_DataFormatByColumn = dataFormatByColumn; } public Map<Integer, FrameworkEnums.EnumDBDataType> getDataTypeByColumn2() { return m_DataTypeByColumn2; } public void setDataTypeByColumn2( Map<Integer, FrameworkEnums.EnumDBDataType> mDataTypeByColumn2) { m_DataTypeByColumn2 = mDataTypeByColumn2; } public Map<Integer, String> getDataFormatByColumn2() { return m_DataFormatByColumn2; } public void setDataFormatByColumn2(Map<Integer, String> mDataFormatByColumn2) { m_DataFormatByColumn2 = mDataFormatByColumn2; } public Object[][] getSourceData() { return m_SourceData; } public void setSourceData(Object[][] sourceData) { m_SourceData = sourceData; } /** * 生成Excel文件 * * @throws Exception */ public void start() throws Exception { try { // 用SXSSFWorkbook设置缓存数据，处理批量写入 Workbook workbook = new SXSSFWorkbook(1000); Sheet sheet = null; if (m_SheetName.isEmpty()) { sheet = workbook.createSheet(); } else { sheet = workbook.createSheet(m_SheetName); } // 写入数据 for (int rIndex = 0; rIndex < m_SourceData.length; rIndex++) { int dataRIndex = rIndex + m_TopSkipRows; Row row = sheet.createRow(dataRIndex); for (int cIndex = 0; cIndex < m_SourceData[rIndex].length; cIndex++) { int dataCIndex = cIndex + m_TopSkipCols; Map<Integer, FrameworkEnums.EnumDBDataType> dataTypeByColumn = m_DataTypeByColumn; Map<Integer, String> dataFormatByColumn = m_DataFormatByColumn; if ((m_DataType2SartRow > 0) && (dataRIndex >= m_DataType2SartRow)) { dataTypeByColumn = m_DataTypeByColumn2; dataFormatByColumn = m_DataFormatByColumn2; } Cell cell = row.createCell(dataCIndex); Object value = m_SourceData[rIndex][cIndex]; if (value == null) { continue; } // 设置但单元格值样式 String dataFormat = dataFormatByColumn.get(dataCIndex); if ((dataFormat != null) && ("".equals(dataFormat) == false)) { CellStyle style = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); style.setDataFormat(format.getFormat(dataFormat)); cell.setCellStyle(style); } // 设置单元格的值和值类型 FrameworkEnums.EnumDBDataType dataType = dataTypeByColumn .get(dataCIndex); if (dataType != null) { switch (dataType) { case AnsiStringFixedLength: case AnsiString: case String: case StringFixedLength: cell.setCellValue(value.toString()); break; case Int: int intVal = 0; try { intVal = Integer.parseInt(value.toString()); } catch (Exception e) { try { double tmpVal = Double.parseDouble(value .toString()); intVal = (int) tmpVal; } catch (Exception ex) { String .format( "转换成int失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage()); } } cell.setCellValue(intVal); break; case Double: double doubleVal = 0.0d; try { doubleVal = Double .parseDouble(value.toString()); } catch (Exception e) { throw new Exception( String .format( "转换成double失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(doubleVal); break; case Float: float floatVal = 0.0f; try { floatVal = Float.parseFloat(value.toString()); } catch (Exception e) { throw new Exception( String .format( "转换成float失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(floatVal); break; case Long: long longVal = 0l; try { longVal = Long.parseLong(value.toString()); } catch (Exception e) { throw new Exception( String .format( "转换成long失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(longVal); break; case Short: short shortVal = (short) 0; try { shortVal = Short.parseShort(value.toString()); } catch (Exception e) { throw new Exception( String .format( "转换成short失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(shortVal); break; case Date: SimpleDateFormat simpleDateFormat = new SimpleDateFormat( "yyyy-MM-dd"); Date dateVal = null; try { String dateString = simpleDateFormat .format(value); dateVal = simpleDateFormat.parse(dateString); } catch (Exception e) { throw new Exception( String .format( "转换成Date失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(dateVal); break; case DateTime: case FullDateTime: simpleDateFormat = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss"); dateVal = null; try { String dateString = simpleDateFormat .format(value); dateVal = simpleDateFormat.parse(dateString); } catch (Exception e) { throw new Exception( String .format( "转换成DateTime失败,Row:%s,Column:%s,Message:%s！", rIndex, cIndex, e .getMessage())); } cell.setCellValue(dateVal); break; default: cell.setCellValue(value.toString()); break; } } else { cell.setCellValue(value.toString()); } } } // 写入文件 OutputStream outputStream = new FileOutputStream(m_FileName); workbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("start:%s", e.getMessage())); } } /** * 设置单元格的值（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param row * 行索引 * @param col * 列索引 * @param value * 值（暂时只支持字符串） * @throws Exception */ public void setCellData(int sheetIndex, int row, int col, String value) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); Row mRow = sheet.getRow(row); if (mRow == null) { mRow = sheet.createRow(row); } Cell mCell = mRow.createCell(col); mCell.setCellValue(value); // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setCellData:%s", e.getMessage())); } } /** * 设置单元格的值，支持/n自动换行（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param row * 行索引 * @param col * 列索引 * @param value * 值（暂时只支持字符串） * @throws Exception */ public void setWrapCellData(int sheetIndex, int row, int col, String value) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); Row mRow = sheet.getRow(row); if (mRow == null) { mRow = sheet.createRow(row); } Cell mCell = mRow.createCell(col); mCell.setCellValue(value); // 设置自动换行 CellStyle style = workbook.createCellStyle(); style.setWrapText(true); mCell.setCellStyle(style); // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setWrapCellData:%s", e .getMessage())); } } /** * 设置单元格数据的水平垂直对齐方式（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 行数 * @param startCol * 开始列，从0开始 * @param colNum * 列数 * @param alignment * 水平展示方式 ALIGN_GENERAL = 0 ALIGN_LEFT = 1 ALIGN_CENTER = 2 * ALIGN_RIGHT = 3 ALIGN_FILL = 4 ALIGN_JUSTIFY = 5 * ALIGN_CENTER_SELECTION = 6 * @param verticalAlignment * 竖直展示方式 VERTICAL_TOP = 0 VERTICAL_CENTER = 1 VERTICAL_BOTTOM = * 2 VERTICAL_JUSTIFY = 3 * @throws Exception */ public void setAlignment(int sheetIndex, int startRow, int rowNum, int startCol, int colNum, short alignment, short verticalAlignment) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); // 设置单元格样式 for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { Cell cell = row.getCell(cIndex); if (cell == null) { cell = row.createCell(cIndex); } CellStyle style = cell.getCellStyle(); // 判断是否已经创建过 if (style.getIndex() == 0) { style = workbook.createCellStyle(); } style.setAlignment(alignment); style.setVerticalAlignment(verticalAlignment); cell.setCellStyle(style); } } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String .format("setAlignment:%s", e.getMessage())); } } /** * 设置单元格的字体及大小（先执行Start方法） * * @param sheetIndex * Excel索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 设置的行数 * @param startCol * 开始列，从0开始 * @param colNum * 设置的列数 * @param font * 字体：TimesRoman, Courier, Arial等 风格：三个常量 lFont.PLAIN, Font.BOLD, * Font.ITALIC 字号：字的大小（磅数） 字体名称和大小 BOLDWEIGHT_NORMAL = 400; * BOLDWEIGHT_BOLD = 700 * @throws Exception */ public void setFont(int sheetIndex, int startRow, int rowNum, int startCol, int colNum, java.awt.Font font) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Font mFont = workbook.createFont(); mFont.setFontName(font.getFontName()); mFont.setFontHeightInPoints((short) font.getSize()); if (font.isBold() == true) { mFont.setBoldweight((short) 700); } else { mFont.setBoldweight((short) 400); } Sheet sheet = workbook.getSheetAt(sheetIndex); // 设置单元格样式 for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { Cell cell = row.getCell(cIndex); if (cell == null) { cell = row.createCell(cIndex); } CellStyle style = cell.getCellStyle(); // 判断是否已经创建过 if (style.getIndex() == 0) { style = workbook.createCellStyle(); } style.setFont(mFont); cell.setCellStyle(style); } } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setFont:%s", e.getMessage())); } } /** * 设置单元格的字体、大小、颜色（先执行Start方法） * * @param sheetIndex * Excel索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 设置的行数 * @param startCol * 开始列，从0开始 * @param colNum * 设置的列数 * @param font * 字体：TimesRoman, Courier, Arial等 风格：三个常量 lFont.PLAIN, Font.BOLD, * Font.ITALIC 字号：字的大小（磅数） 字体名称和大小 BOLDWEIGHT_NORMAL = 400; * BOLDWEIGHT_BOLD = 700 * @param color * 颜色 new Color(54, 201, 201) * @throws Exception */ public void setFont(int sheetIndex, int startRow, int rowNum, int startCol, int colNum, java.awt.Font font, java.awt.Color color) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); XSSFFont mFont = workbook.createFont(); mFont.setFontName(font.getFontName()); mFont.setFontHeightInPoints((short) font.getSize()); mFont.setColor(new XSSFColor(color)); if (font.isBold() == true) { mFont.setBoldweight((short) 700); } else { mFont.setBoldweight((short) 400); } Sheet sheet = workbook.getSheetAt(sheetIndex); // 设置单元格样式 for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { Cell cell = row.getCell(cIndex); if (cell == null) { cell = row.createCell(cIndex); } CellStyle style = cell.getCellStyle(); // 判断是否已经创建过 if (style.getIndex() == 0) { style = workbook.createCellStyle(); } style.setFont(mFont); cell.setCellStyle(style); } } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setFont2:%s", e.getMessage())); } } /** * 设置背景色（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 行数 * @param startCol * 开始列，从0开始 * @param colNum * 列数 * @param color * 颜色 new Color(54, 201, 201) * @throws Exception */ public void setBackgroundColor(int sheetIndex, int startRow, int rowNum, int startCol, int colNum, java.awt.Color color) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); // 设置单元格样式 for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { Cell cell = row.getCell(cIndex); if (cell == null) { cell = row.createCell(cIndex); } XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle(); // 判断是否已经创建过 if (style.getIndex() == 0) { style = workbook.createCellStyle(); } style.setFillForegroundColor(new XSSFColor(color)); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); } } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setBackgroudColor:%s", e .getMessage())); } } /** * 设置合并单元格（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param firstRow * 开始行 * @param lastRow * 结束行 * @param firstCol * 开始列 * @param lastCol * 结束列 * @throws Exception */ public void setMerge(int sheetIndex, int firstRow, int lastRow, int firstCol, int lastCol) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setMerge:%s", e.getMessage())); } } /** * 设置单元格的边框（先执行Start方法） * * @param sheetIndex * Excel索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 设置的行数 * @param startCol * 开始列，从0开始 * @param colNum * 需要设置的列数 * @throws Exception */ public void setBorder(int sheetIndex, int startRow, int rowNum, int startCol, int colNum) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); // 设置单元格样式 for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { Cell cell = row.getCell(cIndex); if (cell == null) { cell = row.createCell(cIndex); } CellStyle style = cell.getCellStyle(); // 判断是否已经创建过 if (style.getIndex() == 0) { style = workbook.createCellStyle(); } style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); cell.setCellStyle(style); } } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setBorder:%s", e.getMessage())); } } /** * 设置行的高度（先执行Start方法） 说明：与Excel不是很对应，请多设置几次高度值 * * @param sheetIndex * Excel索引，从0开始 * @param startRow * 开始行，从0开始 * @param rowNum * 行的数量 * @param height * 设置的高度 * @throws Exception */ public void setRowHeight(int sheetIndex, int startRow, int rowNum, short height) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); // 高度特殊处理 height = (short) (height * 20); Sheet sheet = workbook.getSheetAt(sheetIndex); for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) { Row row = sheet.getRow(rIndex); if (row == null) { row = sheet.createRow(rIndex); } row.setHeight(height); } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setAutoFit:%s", e.getMessage())); } } /** * 设置列的宽度（先执行Start方法） 说明：与Excel不是很对应，请多设置几次宽度值 * * @param sheetIndex * Excel索引，从0开始 * @param startCol * 开始列，从0开始 * @param colNum * 列数 * @param width * 宽度 * @throws Exception */ public void setColumnWidth(int sheetIndex, int startCol, int colNum, int width) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); // 宽度特殊处理 width = width * 275; Sheet sheet = workbook.getSheetAt(sheetIndex); for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { sheet.setColumnWidth(cIndex, width); } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setAutoFit:%s", e.getMessage())); } } /** * 设置列自适应（先执行Start方法） * * @param sheetIndex * Sheet的索引，从0开始 * @param startCol * 开始列，从0开始 * @param colNum * 列数量 * @throws Exception */ public void setAutoFit(int sheetIndex, int startCol, int colNum) throws Exception { try { InputStream inputStream = new FileInputStream(m_FileName); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); inputStream.close(); Sheet sheet = workbook.getSheetAt(sheetIndex); for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) { sheet.autoSizeColumn(cIndex); } // 写入文件，采用分批写入的方式进行写入 OutputStream outputStream = new FileOutputStream(m_FileName); SXSSFWorkbook sXSSFWorkbook = new SXSSFWorkbook(workbook, 1000); sXSSFWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("setAutoFit:%s", e.getMessage())); } } /** * 合并Excel * * @param toFileName * 写入的文件路径 * @param toSheetIndex * 写入的文件SheetIndex，如果写在最后请设置-1，否则请在Sheet数量范围内 * @param fromFileName * 读取的文件路径 * @param fromSheetIndex * 读取的文件SheetIndex * @throws Exception */ public void mergeExcel(String toFileName, int toSheetIndex, String fromFileName, int fromSheetIndex) throws Exception { try { // 1、打开Excel1 InputStream inputStream = new FileInputStream(toFileName); XSSFWorkbook toWorkbook = new XSSFWorkbook(inputStream); inputStream.close(); // 2、打开Excel2 inputStream = new FileInputStream(fromFileName); XSSFWorkbook fromWorkbook = new XSSFWorkbook(inputStream); inputStream.close(); // 3、复制Sheet，放在ToExcel1的Sheet上 copySheet(toWorkbook, toSheetIndex, fromWorkbook, fromSheetIndex); // 写入Excel1文件 OutputStream outputStream = new FileOutputStream(toFileName); toWorkbook.write(outputStream); outputStream.close(); } catch (Exception e) { throw new Exception(String.format("mergeExcel:%s", e.getMessage())); } } private void copySheet(XSSFWorkbook toWorkbook, int toSheetIndex, XSSFWorkbook fromWorkbook, int fromSheetIndex) throws Exception { Sheet fromSheet = fromWorkbook.cloneSheet(fromSheetIndex); String sheetName = fromSheet.getSheetName().replace("(2)", ""); Sheet toSheet = toWorkbook.getSheet(sheetName); if (null == toSheet) { toSheet = toWorkbook.createSheet(sheetName); if(toSheetIndex >= 0){ toWorkbook.setSheetOrder(sheetName, toSheetIndex); } } else { throw new Exception("相同名称的Sheet已存在"); } // 1、合并单元格 for (int mrIndex = 0; mrIndex < fromSheet.getNumMergedRegions(); mrIndex++) { CellRangeAddress cellRangeAddress = fromSheet .getMergedRegion(mrIndex); toSheet.addMergedRegion(cellRangeAddress); } // 2、单元格赋值，样式等 Map<Integer, Integer> setColumnWidthIndex = new HashMap<Integer, Integer>(); Map<Short, Short> setFontIndex = new HashMap<Short, Short>(); for (int rIndex = fromSheet.getFirstRowNum(); rIndex <= fromSheet .getLastRowNum(); rIndex++) { Row fromRow = fromSheet.getRow(rIndex); if (null == fromRow) { continue; } Row toRow = toSheet.createRow(rIndex); // 设置行高，自动行高即可 //toRow.setHeight(fromRow.getHeight()); // 设置Cell的值和样式 for (int cIndex = fromRow.getFirstCellNum(); cIndex <= fromRow .getLastCellNum(); cIndex++) { Cell fromCell = fromRow.getCell(cIndex); if (null == fromCell) { continue; } Cell toCell = toRow.createCell(cIndex); // 设置列宽 Integer isSet = setColumnWidthIndex.get(cIndex); if (null == isSet) { toSheet.setColumnWidth(cIndex, fromSheet .getColumnWidth(cIndex)); setColumnWidthIndex.put(cIndex, cIndex); } // 设置单元格样式 CellStyle fromCellStyle = fromCell.getCellStyle(); if (fromCellStyle.getIndex() != 0) { XSSFCellStyle toCellStyle = toWorkbook.createCellStyle(); // 文字展示样式 String fromDataFormat = fromCellStyle.getDataFormatString(); if ((null != fromDataFormat) && ("".equals(fromDataFormat) == false)) { DataFormat toDataFormat = toWorkbook.createDataFormat(); toCellStyle.setDataFormat(toDataFormat .getFormat(fromDataFormat)); } // 文字换行 toCellStyle.setWrapText(fromCellStyle.getWrapText()); // 文字对齐方式 toCellStyle.setAlignment(fromCellStyle.getAlignment()); toCellStyle.setVerticalAlignment(fromCellStyle .getVerticalAlignment()); // 单元格边框 toCellStyle.setBorderLeft(fromCellStyle.getBorderLeft()); toCellStyle.setBorderRight(fromCellStyle.getBorderRight()); toCellStyle.setBorderTop(fromCellStyle.getBorderTop()); toCellStyle .setBorderBottom(fromCellStyle.getBorderBottom()); // 字体颜色，大小 short fromFontIndex = fromCellStyle.getFontIndex(); XSSFFont fromFont = fromWorkbook.getFontAt(fromFontIndex); Short toFontIndex = setFontIndex.get(fromFontIndex); if (null == toFontIndex) { XSSFFont toFont = toWorkbook.createFont(); toFont.setBoldweight(fromFont.getBoldweight()); toFont.setFontName(fromFont.getFontName()); toFont.setFontHeightInPoints(fromFont .getFontHeightInPoints()); toFont.setColor(fromFont.getXSSFColor()); toFont.setBold(fromFont.getBold()); toCellStyle.setFont(toFont); // 设置的Font加入集合中 toFontIndex = toFont.getIndex(); setFontIndex.put(fromFontIndex, toFontIndex); } else { XSSFFont toFont = toWorkbook.getFontAt(toFontIndex); toCellStyle.setFont(toFont); } // 背景色 XSSFColor fillForegroundColor = (XSSFColor) fromCellStyle .getFillForegroundColorColor(); toCellStyle.setFillForegroundColor(fillForegroundColor); toCellStyle.setFillPattern(fromCellStyle.getFillPattern()); toCell.setCellStyle(toCellStyle); } int fromCellType = fromCell.getCellType(); switch (fromCellType) { case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; default: toCell.setCellValue(fromCell.getStringCellValue()); break; } } } }}
阅读全文
0 0
- java excel poi
- Java读写Excel-POI
- Java操作Excel Poi
- Java操作Excel Poi
- POI Java操作Excel
- java poi excel
- java poi excel 操作
- java poi 导出excel
- JAVA POI EXCEL
- Java POI EXCEL
- java poi excel
- Java POI 读取Excel
- java poi读取excel
- java poi操作EXCEL
- java poi修改excel
- java poi读写excel
- java Excel 读取 poi
- Java操作Excel Poi
- Unity实时获取麦克风音频，处理成可视化
- 大型高并发网站之查询性能优化（综合篇）
- forahead adder 超前进位加法器
- OpenCV VideoCapture类使用
- sed:s命令
- Java Excel POI
- 关于线性模型出现非正定矩阵的问题解释
- IDEA Maven Project 视图找不到解决
- ANSYS Products v18.1.1 Update Only Multilingual Win64 1DVD
- springboot环境的搭建
- TPLink路由器登陆密码怎么破解
- 纯电动汽车结构分类和整体认识
- Xcode9出现错误safe area layout guide before ios 9
- vmware中安装centos7 无法连通主机的解决办法
