1、使用
String toFileName = "E:\\sheet1.xlsx";
String fromFileName = "E:\\sheet2.xlsx"; MemoryToExcel memoryToExcel = new MemoryToExcel();
memoryToExcel.mergeExcel(toFileName, , fromFileName, ); // 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();
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;
}
}
}
} }
3、源码下载