使用POI技术导出和导入Excel

由于在最近项目中需要对Excel文件进行导出和导入，所以从网上参考了一些资料，自己编写了一个工具类。

这个工具类可以解决大部分Excel文件导出导入所需的功能，适用性较强，支持Excel2003,2007等高级版本。同时丰富了对CSV文件的导出和导入的功能。

工具类在附件中，分享给大家，如有错误请大家提出，谢谢！

下面对这个工具类的用法给大家讲解一下。

Excel导入方法 public static Map<String, List<Map<String, Object>>> readExcel(InputStream in) throws Exception {}

该方法传入Excel文件（支持含有合并单元格的Excel文件）的InputStream参数即可。

得到数据类型为 Map<String, List<Map<String, Object>>>

举个例子来说明这个数据类型的代表的含义。

假如我们有一个test.xlsx文件，这个文件中有user工作簿(sheet)和class工作簿这两个Sheet表

则最外层的map中的key:为sheet name,   value为一个sheet中所有数据。

里层的Map<String,Object>代表一个sheet中一行(row)的数据。Object是因为单元格可能有各种数据类型的数据。

其中key为单元格(cell)的名称，value为单元格的值。

List<Map<String,Object>>就代表一个单元格中所有行的数据了。

好了，附上我使用该方法的代码

/* total excel sheets */

//读取一个Excel文件
Map<String, List<Map<String, Object>>> sheetMap = ExcelAndCSVUtils.readExcel(file.getInputStream());

//遍历
for (Map.Entry<String, List<Map<String, Object>>> sheet : sheetMap.entrySet()) {
List<Map<String, Object>> rows = sheet.getValue();
for (Map<String, Object> row : rows) {
System.out.println(row.get("name"));
}
}

大家可以自己导入一个excel文件进去试试。

CSV导入方法public static List<Map<String, Object>> readCSV(InputStream in) throws Exception {}

这个方法和Excel方法一致，也是传入CSV文件InputStream流进来。由于CSV没有多个Sheet。则这里得到

数据类型为List<Map<String, Object>> 代表只有一个sheet。

Excel导出方法public static void exportExcel(String title, String rowTopTitle, Object[] headers, List<?> dataSet,
OutputStream out, int mergedCellIndexs[]) throws Exception {}

参数代表的意思:title即这个表格的大标题(不能为空)，rowTopTitle即这个表格的小标题（可以为空），

headers即一行单元格的名称，dataSet数据集。out即一个Excel文件的输出流，mergedCellIndexs即哪一列

相同单元格值的需要合并（可以为空）

List<?> dataSet 可接受两种数据类型。

假如我们有一个User类需要导出。里面有属性private String name,private int age.

则我们可以传入数据集为List<User>，将自动填充。这里注意：name和age定义时的顺序对应单元格的顺序。

如上所示的name和age,导出的时候一行数据中单元格name在左边，单元格age在右边。以此顺序。

第二种数据类型:我们可以使用自定义的数据类型 List<Object[]>，这中数据类型具有很大的灵活性。

Obejct[]即代表一行数据。

举个例子用该方法导出的例子:

               List<AlarmReportVO> alarmList = alarmManger.getAlarmReports(json,timeOffsetUTC);

                     // alarm excel small titile
String[] smallHeaders = { "Alarm Level", "Critical", "Major", "Warning", 
     "Indeterminate", "Minor", "Cleared", "Total" };
// table title
Object[] headers = { "Time", "Ne Group", "Ne Type",  "Equip Num", "Ne Identity", "Alarm Type",
smallHeaders };
// These cells in the alert report table require rows with the same value.
int[] mergedRowIndexs = { 0, 1, 2, 3, 4 };
ExcelAndCSVUtils.exportExcel("Alarm Report",null, headers,alarmList , out,mergedRowIndexs );

导出的结果如下图：

CSV导出：public static void exportCustomCSV(String filePath,String[]topRowTitle,String[] headers,List<String[]> datas) throws Exception {} 

如同Excel导出，filePath表示导出CSV文件的路径。其他和导出Excel的参数意思一致，导出CSV没有合并单元格的参数。

如有不明白，请阅读源码中的代码，自己就理解。源码如下：

package com.casa.hems.common.utils;


import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;


import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;


/**
 * 
 * @author Zheng Chuansi
 * @Description:operate Excel or CSV file util
 */
public class ExcelAndCSVUtils {


/**
* 
* @author:Zheng Chuansi
* @Description:read excel
* @time:2016-9-12 14:11:02
* @param in: file inputstream
* @return
* @throws Exception
*/
public static Map<String, List<Map<String, Object>>> readExcel(InputStream in) throws Exception {
/* create work book */
Workbook wb = WorkbookFactory.create(in);
/* sheet map of one excel file */
Map<String, List<Map<String, Object>>> sheetMap = new LinkedHashMap<>();
/* get sheet count */
int sheet_numbers = wb.getNumberOfSheets();
for (int i = 0; i < sheet_numbers; i++) {
/* store the read data for a sheet */
List<Map<String, Object>> list_rows = new ArrayList<>();
// get a sheet
Sheet sheet = wb.getSheetAt(i);
/* title row */
Row titleRow = sheet.getRow(0);
Row row = null;
for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
row = sheet.getRow(j);
/* key:column title,value:column value */
Map<String, Object> messages = new HashMap<String, Object>();
for (Cell c : row) {
if (null == c) {
continue;
}
/* Gets the title of the column */
Cell titleCell = titleRow.getCell(c.getColumnIndex());
if (null != titleCell) {
String title = titleCell.getStringCellValue().trim();
boolean isMerge = isMergedRegion(sheet, j, c.getColumnIndex());
/* Determines whether there is a merged cell */
if (isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
messages.put(title, rs);
} else {
messages.put(title, getCellValue(c));
}
}
}
/* add a row */
if (null != messages && !messages.isEmpty()) {
/* add a row */
list_rows.add(messages);
}
}
/* add a sheet */
sheetMap.put(sheet.getSheetName(), list_rows);
}
return sheetMap;
}


/**
* 
* @author:Zheng Chuansi
* @Description:Gets the value of the merged cell
* @time:2016-9-12 11:44:17
* @param sheet
* @param row
* @param column
* @return
*/
private static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();


if (row >= firstRow && row <= lastRow) {


if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}


return null;
}


/**
* 
* @author:Zheng Chuansi
* @Description:Determines whether the specified cell is a merged cell
* @time:2016-9-11 13:45:26
* @param sheet
* @param row
* @param column
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}


/**
* 
* @author:Zheng Chuansi
* @Description:Gets the value of the cell
* @time:2016-9-11 13:45:59
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
if (cell == null)
return "";
String cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
BigDecimal bg = new BigDecimal(cell.getNumericCellValue());
cellValue = bg.toPlainString();
break;
default:
cellValue = "";
}
return cellValue;
}


/**
* 
* @author:Zheng Chuansi
* @Description: read CSV file
* @time:2016-9-13 9:27:11
* @param filePath
* @throws Exception
*/
public static List<Map<String, Object>> readCSV(InputStream in) throws Exception {
CsvReader csv = new CsvReader(in, Charset.forName("UTF-8"));
csv.readHeaders();
/* a table key:column name,value:column value */
List<Map<String, Object>> list = new ArrayList<>();
while (csv.readRecord()) {
Map<String, Object> messages = new HashMap<String, Object>();
for (int i = 0; i < csv.getColumnCount(); i++) {
messages.put(csv.getHeader(i), csv.get(i));
}
list.add(messages);
}
return list;
}


/********************* excel export *****************************/
/**
* 
* @author:Zheng Chuansi
* @Description:export excel method.
* @time:2016-11-17 11:01:50
* @param title:excel table title.
* @param rowTopTitle:top row title.
* @param headers:data row title.
* @param dataSet:export data format
* @param out
* @param mergedCellIndexs: Set some columns of the table to merge row.
* @return
* @throws Exception
*/
public static void exportExcel(String title, String rowTopTitle, Object[] headers, List<?> dataSet,
OutputStream out, int mergedCellIndexs[]) throws Exception {
exportExcelData(title, rowTopTitle, headers, dataSet, out, "yyyy-MM-dd hh:mm:ss", mergedCellIndexs);
}


/**
* 
* @author:Zheng Chuansi
* @Description:export data
* @time:2016-11-17 10:02:22
* @param title:excel table title.
* @param rowTopTitle:top row title.
* @param headers:data row title.
* @param dataSet:export data format.
* @param out
* @param pattern:time format
* @param mergedCellIndexs: Set some columns of the table to merge row.
* @return
* @throws Exception
*/
private static void exportExcelData(String title, String rowTopTitle, Object[] headers, Collection<?> dataSet,
OutputStream out, String pattern, int[] mergedCellIndexs) throws Exception {
//dafult no rowTopTitle
int rowTopTitleFlag = 0;
if (!StringUtil.isNullOrEmpty(rowTopTitle)) {
//appear 
rowTopTitleFlag = 1;
}
// Declare a workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// create a table
HSSFSheet sheet = workbook.createSheet(title);
// Set the default column width of the table to 13 bytes.
sheet.setDefaultColumnWidth((short) 16);
HSSFCellStyle rowTitleStyle = createRowTitleStyle(workbook);
// Declare a top-level manager for drawing
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// Create a row header
HSSFRow row = sheet.createRow(2 + rowTopTitleFlag);
int bigTititleLength = headers.length - 1;
for (short i = 0; i < headers.length; i++) {
// Create header for the cell
HSSFCell cell = row.createCell(i);
// Create the style of heading cell
cell.setCellStyle(rowTitleStyle);
Object titleObj = headers[i];
if (titleObj instanceof String) {
// Merge the cell of header row.
int mergedIndex = sheet.addMergedRegionUnsafe(
new CellRangeAddress((2 + rowTopTitleFlag), (3 + rowTopTitleFlag), i, i));
CellRangeAddress region = sheet.getMergedRegion(mergedIndex);
// Set cell region border style.
setRegionBorder(region, sheet, workbook);
HSSFRichTextString text = new HSSFRichTextString(titleObj.toString());
cell.setCellValue(text);
} else if (titleObj instanceof String[]) {
String smallTitles[] = (String[]) titleObj;
// Merge subtitle cells under headings
int mergedIndex = sheet.addMergedRegion(new CellRangeAddress((2 + rowTopTitleFlag),
(2 + rowTopTitleFlag), i, i + smallTitles.length - 2));
CellRangeAddress region = sheet.getMergedRegion(mergedIndex);
// Set cell region border style.
setRegionBorder(region, sheet, workbook);
HSSFRichTextString text = new HSSFRichTextString(smallTitles[0]);
cell.setCellValue(text);
row = sheet.createRow(3 + rowTopTitleFlag);
bigTititleLength += smallTitles.length - 2;
for (int j = 1; j < smallTitles.length; j++) {
// Create a subtitle cell under the heading
HSSFCell smallTitleCell = row.createCell(i + j - 1);
smallTitleCell.setCellStyle(rowTitleStyle);
smallTitleCell.setCellValue(smallTitles[j]);
}
}
}
// Create the table header
CreateTableTitle(title, bigTititleLength, workbook, sheet);
//Whether to create rowTopTitle
if (rowTopTitleFlag > Constants.ZERO) {
CreateRowTopTitle(rowTopTitle, bigTititleLength, workbook, sheet);
}
// Traversing the data collection,create data row.
fillExcelData(dataSet, pattern, workbook, sheet, patriarch, 
mergedCellIndexs, bigTititleLength,rowTopTitleFlag);
workbook.write(out);
}


/**
* 
* @author:Zheng Chuansi
* @Description:Create the table header
* @time:2016-11-17 09:14:11
* @param title:table title.
* @param lastColumn: last column.
* @param workbook
* @param sheet
*/
private static void CreateTableTitle(String title, int lastColumn, HSSFWorkbook workbook, HSSFSheet sheet) {
// Create a table header row
HSSFRow tilteRow = sheet.createRow(0);
HSSFCell cellTitle = tilteRow.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, lastColumn));
// Set font
HSSFFont font = workbook.createFont();
// Set font size
font.setFontHeightInPoints((short) 16);
font.setBold(true);
// Set font name
// font.setFontName("Courier New");
// Set Style
HSSFCellStyle style = workbook.createCellStyle();
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setFillForegroundColor(HSSFColor.RED.index);
// The style used in application to set the font.
style.setFont(font);
// Set whether to wrap.
style.setWrapText(true);
// Sets the horizontal alignment style to center-aligned.
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Sets the vertical alignment style to center-aligned.
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellTitle.setCellStyle(style);
cellTitle.setCellValue(title);
}

/**
* 
* @author:Zheng Chuansi
* @Description:create top Title
* @time:2016-11-30 15:51:02
* @param rowTopTitle
* @param lastColumn
* @param workbook
* @param sheet
*/
private static void CreateRowTopTitle(String rowTopTitle,int lastColumn, HSSFWorkbook workbook, HSSFSheet sheet) {
// Create a table header row
HSSFRow tilteRow = sheet.createRow(2);
HSSFCell cellTitle = tilteRow.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, lastColumn));
// Set font
HSSFFont font = workbook.createFont();
// Set font size
font.setFontHeightInPoints((short) 13);
// Set font name
// font.setFontName("Courier New");
// Set Style
HSSFCellStyle style = workbook.createCellStyle();
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setFillForegroundColor(HSSFColor.RED.index);
// The style used in application to set the font.
style.setFont(font);
// Set whether to wrap.
style.setWrapText(false);
// Sets the horizontal alignment style to center-aligned.
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// Sets the vertical alignment style to center-aligned.
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellTitle.setCellStyle(style);
cellTitle.setCellValue(rowTopTitle);
}


/**
* 
* @author:Zheng Chuansi
* @Description: Fill in data for the table based on the different data types.
* @time:2016-11-17 10:59:12
* @param dataSet
* @param pattern
* @param workbook
* @param sheet
* @param patriarch
* @param mergedRowIndexs: Set some columns of the table to merge row.
* @param The number of cells to create a row of data based on the length of the header.
* @throws NoSuchMethodException
* @throws IllegalAccessException
* @throws InvocationTargetException
*/
private static void fillExcelData(Collection<?> dataSet, String pattern, HSSFWorkbook workbook, HSSFSheet sheet,
HSSFPatriarch patriarch, int[] mergedRowIndexs, int valueArray,int rowTopTitleFlag)
throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
HSSFRow row;
Iterator<?> it = dataSet.iterator();
int index = 3 + rowTopTitleFlag;
HSSFCellStyle style = createDataCellStyle(workbook);
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
Object t = (Object) it.next();
/* if the generic type is Object[] */
if (t instanceof Object[]) {
Object[] valueArr = (Object[]) t;
for (short i = 0; i < valueArr.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
Object value = valueArr[i];
fillData(pattern, workbook, sheet, patriarch, row, index, i, cell, value);
}
} else { // if the generic type is other object
/*
* Using reflection, according to javaBean attributes of the
* order, dynamic call getXxx () method to get the attribute
* value
*/
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i <= valueArray; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class<?> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
fillData(pattern, workbook, sheet, patriarch, row, index, i, cell, value);
}
}
}
if(null == mergedRowIndexs || mergedRowIndexs.length <= 0){
return;
}
// merge rows with the same value.
mergedRowRegion(workbook, sheet,rowTopTitleFlag, mergedRowIndexs);
}


/**
* 
* @author:Zheng Chuansi
* @Description:Fill in the table data.
* @time:2016 11-16 16:22:57
* @param pattern
* @param workbook
* @param sheet
* @param patriarch
* @param row
* @param index
* @param i
* @param cell
* @param value
*/
private static void fillData(String pattern, HSSFWorkbook workbook, HSSFSheet sheet, HSSFPatriarch patriarch,
HSSFRow row, int index, short i, HSSFCell cell, Object value) {
// Determine the type of the value after the mandatory type conversion.
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = String.valueOf(bValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
sheet.setColumnWidth(i, textValue.length() * 300);
} else if (value instanceof byte[]) {
// When there are pictures, set the line height to 60px.
row.setHeightInPoints(60);
/*
* Set the column width of the picture to 80px,pay attetion to the
* unit here a conversion.
*/
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// Other data types are treated as simple Strings.
if (null == value) {
textValue = (String) value;
} else {
textValue = value.toString();
if (textValue.length() > Constants.TEN) {
sheet.setColumnWidth(i, textValue.length() * 400);
} else {
sheet.setDefaultColumnWidth((short) 16);
}
}
}
/*
* if it isn't image data,use the regular expression textValue is
* entirely composed of numbers.
*/
if (null != textValue) {
Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// Is the number as double to deal with.
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}


}


/**
* 
* @author:Zheng Chuansi
* @Description:Create the style of the data cell
* @time:2016-11-17 11:31:56
* @param workbook
* @return
*/
private static HSSFCellStyle createDataCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}


/**
* 
* @author:Zheng Chuansi
* @Description:Create the style of the heading cell
* @time:2016-11-17 11:41:43
* @param workbook
* @return
*/
private static HSSFCellStyle createRowTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// Generate another font
HSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 13);
// Apply the font to the current style.
style.setFont(font);
return style;
}


/**
* 
* @author:Zheng Chuansi
* @Description:Sets the border of the merged cell.
* @time:2016-11-17 8:57:53
* @param region
* @param sheet
* @param wb
*/
private static void setRegionBorder(CellRangeAddress region, Sheet sheet, Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// Generate another font
Font font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 14);
// Apply the font to the current style.
style.setFont(font);
setRegionStyle(style, region, sheet);
}


/**
* 
* @author:Zheng Chuansi
* @Description:Sets the style of the merged cell.
* @time:2016-11-17 8:57:04
* @param cs
* @param region
* @param sheet
*/
private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
Row row = sheet.getRow(i);
if (null == row)
row = sheet.createRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
Cell cell = row.getCell(j);
if (null == cell) {
cell = row.createCell(j);
cell.setCellValue("");
}
cell.setCellStyle(cs);
}
}
}


/**
* 
* @author:Zheng Chuansi
* @Description:Merge rows with the same value
* @time:2016-11-22 9:14:22
* @param sheet
* @param params: Sets which table rows need to be merged.
*/
private static void mergedRowRegion(HSSFWorkbook workbook, HSSFSheet sheet, int rowTopTitleFlag,int params[]) {
// The loop needs an array of rows that combine the same value.
for (int i = 0; i < params.length; i++) {
// the last row of the table.
int totalRow = sheet.getLastRowNum();
// Starting from the current fourth line.
int currnetRow = 4 + rowTopTitleFlag;
for (int k = currnetRow; k < totalRow + 1; k++) {
// current row.
HSSFCell currentCell = sheet.getRow(k).getCell(params[i]);
String currentValue = getCellValue(currentCell);
// next row.
HSSFCell nextCell = null;
String nextValue = "";
if (k < totalRow + 1) {
HSSFRow nowRow = sheet.getRow(k + 1);
if (nowRow != null) {
nextCell = nowRow.getCell(params[i]);
nextValue = getCellValue(nextCell);
} else {
nextValue = "";
}
} else {
nextValue = "";
}
// if the value of the current row is equal to
// the value of the next row.
if (currentValue.equals(nextValue)) {
currentCell.setCellValue("");
continue;
} else {
/*
* if the number of rows in the loop is equal to the number
* of rows that need to be merged, consolidation is not
* required.
*/
if (k == currnetRow) {
// and specify the location of the next line
// where the merge lines will start.
currentCell.setCellValue(currentValue);
currnetRow = k + 1;
continue;
}
// merge cell.
sheet.addMergedRegion(new CellRangeAddress(currnetRow, k, params[i], params[i]));
// sets the value after the merge line.
HSSFCell nowCell = sheet.getRow(currnetRow).getCell(params[i]);
nowCell.setCellValue(currentValue);
// nowCell.setCellStyle(createDataCellStyle(workbook));
// and re-specify the location where the rows need to be
// merged.
currnetRow = k + 1;
}
}
}
}



/*******************************export CSV file**********************************************/
/**
* 
* @author:Zheng Chuansi
* @Description:List<javaBean> Cast to List<String[]>
* @time:2016-12-6 10:00:02
* @param beanList
* @param cols:The length of the data column to be writen.
* @return
* @throws Exception
*/
private static <T> List<String[]> beanCastToStringArray(List<T> beanList,int cols) throws Exception {
/*beanList size < 1, return null*/
if(beanList.size() < Constants.ONE) {
return null;
}
List<String[]> datas = new ArrayList<>();
/*bean cast to String[]*/
for(T t : beanList) {
Class<? extends Object> classzz = t.getClass();
Field fields[] = classzz.getDeclaredFields();
String[] valueArr = new String[fields.length];
for(int i = 0; i < cols; i++){
String fieldName = fields[i].getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Method method = t.getClass().getMethod(getMethodName, new Class[] {});
Object val = method.invoke(t, new Object[] {});
String value = null;
if(val == null){
value = "";
}else {
value = val.toString();
}
valueArr[i] = value;
}
datas.add(valueArr);
}
return datas;
}

/**
* 
* @author:Zheng Chuansi
* @Description:export CSV file
* @time:2016-12-6 10:04:03
* @param filePath : file absolute path.
* @param topRowTitle :
* @param headers:data title
* @param datas
* @throws Exception
*/
@SuppressWarnings("unchecked")
public static void exportCSV(String filePath,String[]topRowTitle,String[] headers,List<? extends Object> datas) throws Exception {
/*Specifies the file path and data encoding format*/
CsvWriter cw = new CsvWriter(filePath, ',', Charset.forName("UTF-8"));
List<String[]> contents = null;
if(!(datas.get(0) instanceof String[])) {
contents  = beanCastToStringArray(datas,headers.length);
} else {
contents =  (List<String[]>) datas;
}
/*wirte csv file*/
if(null != contents && !contents.isEmpty()){
/*wirte rowTop title*/
if(null != topRowTitle && topRowTitle.length > Constants.ZERO) {
cw.writeRecord(topRowTitle);
}
/*wirte file title*/
cw.writeRecord(headers);
for(String[] content : contents) {
cw.writeRecord(content);
}
}
cw.close();
}
public static void exportCustomCSV(String filePath,String[]topRowTitle,String[] headers,List<String[]> datas) throws Exception {
/*Specifies the file path and data encoding format*/
CsvWriter cw = new CsvWriter(filePath, ',', Charset.forName("UTF-8"));
/*wirte csv file*/
if(null != datas && !datas.isEmpty()){
/*wirte rowTop title*/
if(null != topRowTitle && topRowTitle.length > Constants.ZERO) {
cw.writeRecord(topRowTitle);
}
/*wirte file title*/
cw.writeRecord(headers);
for(String[] data : datas) {
cw.writeRecord(data);
}
}
cw.close();
}

}