Excel 工具类

package com.mx.travel.util;


import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
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.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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;




public class ExcelHelper {


/**
* 生成Excel(2003版本)
* 
* @param JsonData
*            数据
* @param title
*            标题
* @param filePath
*            物理地址
* @return
*/
@SuppressWarnings("deprecation")
public static String getExcel(String JsonData, String title, String filePath) {
String Result = "";
try {
List<LinkedHashMap<String, String>> valuesList = JSON.parseObject(JsonData, new TypeReference<List<LinkedHashMap<String, String>>>() {});


// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(title);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
sheet.setDefaultColumnWidth((short) 15);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式


for (int i = 0; i < valuesList.size(); i++) {
LinkedHashMap<String, String> lmap = valuesList.get(i);
Iterator<String> it = lmap.keySet().iterator();
int j = 0;
while (it.hasNext()) {
Object key = it.next();
HSSFCell cell = row.createCell((short) j);
HSSFRichTextString text = new HSSFRichTextString(key.toString());
cell.setCellValue(text);
cell.setCellStyle(style);
j++;
}
}
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
for (int i = 0; i < valuesList.size(); i++) {
int j = 0;
Iterator<Entry<String, String>> it = valuesList.get(i).entrySet().iterator();
while (it.hasNext()) {
Entry<String, String> et = it.next();
row = sheet.createRow((int) i + 1);
// 第四步，创建单元格，并设置值
HSSFRichTextString text = new HSSFRichTextString(et.getValue());
row.createCell((short) j).setCellValue(text);
j++;
}
}
// 第六步，将文件存到指定位置


FileOutputStream fout = new FileOutputStream(filePath);
wb.write(fout);
fout.close();
Result = "0";
} catch (Exception e) {
e.printStackTrace();
Result = "-1";
}
return Result;
}


/**
* 生成Excel(2007版本以上)
* 
* @param JsonData
* @param title
* @param filePath
* @return
*/
public static String createExcel(String JsonData, String title, String fileName) {
List<LinkedHashMap<String, String>> valuesList = JSON.parseObject(JsonData, new TypeReference<List<LinkedHashMap<String, String>>>() {});
String Result = "";
String filePath = System.getProperty("catalina.home")+"\\temp\\" + fileName + ".xlsx";
try {
// System.out.println("############ 开始创建EXCEL对象：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作薄
XSSFSheet sheet = workBook.createSheet();
workBook.setSheetName(0, title);// 工作簿名称
XSSFFont font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("yahei");
XSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFRow titleRow = sheet.createRow((short) 0);// 第一行标题
LinkedHashMap<String, String> map = valuesList.get(0);
Integer i = 0;
// System.out.println("############ 开始拼装EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
for (Entry<String, String> entry : map.entrySet()) {
XSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getKey());
i++;
}
// System.out.println("############ 开始填充EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFCellStyle style = workBook.createCellStyle();// 创建格式
XSSFFont fontBody = workBook.createFont();
fontBody.setFontName("yahei");
style.setFont(fontBody);
for (int j = 0; j < valuesList.size(); j++) {
LinkedHashMap<String, String> vmap = valuesList.get(j);
XSSFRow row = sheet.createRow((short) j + 1);
int k = 0;
for (Entry<String, String> entry : vmap.entrySet()) {
XSSFCell cell = row.createCell(k, 0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getValue());
// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
// sheet.autoSizeColumn(k); // 自动调整宽度
k++;
}
}
// System.out.println("############ EXCEL数据填充完成：" + org.mx.travel.util.DateUtil.getDateTime());


File file = new File(filePath);
FileOutputStream outStream = new FileOutputStream(file);
workBook.write(outStream);
outStream.flush();
outStream.close();


// System.out.println("############ 写文件流完成：" + org.mx.travel.util.DateUtil.getDateTime());


HttpServletResponse response = ServletActionContext.getResponse();
// File file1 = new File(filePath);
// 取得文件名。
fileName = fileName + ".xlsx";
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1" ));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();


// System.out.println("############ 输出文件流完成：" + org.mx.travel.util.DateUtil.getDateTime());


File fileD = new File(filePath);
if (fileD.isFile() && fileD.exists()) fileD.delete();


// System.out.println("############ 删除文件完成：" + org.mx.travel.util.DateUtil.getDateTime());
Result = "0";
} catch (Exception e) {
File fileD = new File(filePath);
if (fileD.isFile() && fileD.exists()) fileD.delete();
e.printStackTrace();
Result = "-1";
}
return Result;
}

/**
* 生成Excel(2007版本以上),增加样式(冻结窗口)
* @author zhoufuhao
* @param JsonData
* @param title
* @param filePath
* @return
*/
public static String createExcelFreezePane(String JsonData, String title, String fileName, String excelTitles[]) {
List<LinkedHashMap<String, String>> valuesList = JSON.parseObject(JsonData, new TypeReference<List<LinkedHashMap<String, String>>>() {});
String Result = "";
String filePath = System.getProperty("catalina.home")+"\\temp\\" + fileName + ".xlsx";
try {
// System.out.println("############ 开始创建EXCEL对象：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作薄
XSSFSheet sheet = workBook.createSheet();
sheet.createFreezePane(8, 2, 8, 2);// 冻结窗口样式
workBook.setSheetName(0, title);// 工作簿名称
XSSFFont font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("yahei");
XSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFRow titleRow = sheet.createRow((short) 0);// 第一行标题
XSSFRow titleRow2 = sheet.createRow((short) 1);// 第一行标题


LinkedHashMap<String, String> map = valuesList.get(0);
Integer i = 0;
Integer m = 0;
// System.out.println("############ 开始拼装EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
for (Entry<String, String> entry : map.entrySet()) {
XSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getKey());
i++;
}
for (int j = 0; j < excelTitles.length; j++) {
XSSFCell cell2 = titleRow2.createCell(m, 1);
cell2.setCellStyle(cellStyle);
cell2.setCellType(XSSFCell.CELL_TYPE_STRING);
cell2.setCellValue(excelTitles[j]);
m++;
}
// System.out.println("############ 开始填充EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFCellStyle style = workBook.createCellStyle();// 创建格式
XSSFFont fontBody = workBook.createFont();
fontBody.setFontName("yahei");
style.setFont(fontBody);
for (int j = 0; j < valuesList.size(); j++) {
LinkedHashMap<String, String> vmap = valuesList.get(j);
XSSFRow row = sheet.createRow((short) j + 2);
int k = 0;
for (Entry<String, String> entry : vmap.entrySet()) {
XSSFCell cell = row.createCell(k, 0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getValue());
// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
// sheet.autoSizeColumn(k); // 自动调整宽度
k++;
}
}
// System.out.println("############ EXCEL数据填充完成：" + org.mx.travel.util.DateUtil.getDateTime());

File file = new File(filePath);
FileOutputStream outStream = new FileOutputStream(file);
workBook.write(outStream);
outStream.flush();
outStream.close();

// System.out.println("############ 写文件流完成：" + org.mx.travel.util.DateUtil.getDateTime());

HttpServletResponse response = ServletActionContext.getResponse();
// File file1 = new File(filePath);
// 取得文件名。
fileName = fileName + ".xlsx";
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1" ));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();

// System.out.println("############ 输出文件流完成：" + org.mx.travel.util.DateUtil.getDateTime());

File fileD = new File(filePath);
if (fileD.isFile() && fileD.exists()) fileD.delete();

// System.out.println("############ 删除文件完成：" + org.mx.travel.util.DateUtil.getDateTime());
Result = "0";
} catch (Exception e) {
File fileD = new File(filePath);
if (fileD.isFile() && fileD.exists()) fileD.delete();
e.printStackTrace();
Result = "-1";
}
return Result;
}
/**
* 生成Excel,弹出下载框(2007版本以上)
* @param JsonData
* @param title
* @return
* @author  liuyandong
* @date 创建时间：2016-4-13 下午2:35:32 
* @version 1.0
*/
public static String downloadExcel(String JsonData, String title, HttpServletResponse response) {
List<LinkedHashMap<String, String>> valuesList = JSON.parseObject(JsonData, new TypeReference<List<LinkedHashMap<String, String>>>() {});
String result = "";
try {
// System.out.println("############ 开始创建EXCEL对象：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作薄
XSSFSheet sheet = workBook.createSheet();
workBook.setSheetName(0, title);// 工作簿名称
XSSFFont font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("yahei");
XSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFRow titleRow = sheet.createRow((short) 0);// 第一行标题
LinkedHashMap<String, String> map = valuesList.get(0);
Integer i = 0;
// System.out.println("############ 开始拼装EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
for (Entry<String, String> entry : map.entrySet()) {
XSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getKey());
i++;
}
// System.out.println("############ 开始填充EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFCellStyle style = workBook.createCellStyle();// 创建格式
XSSFFont fontBody = workBook.createFont();
fontBody.setFontName("yahei");
style.setFont(fontBody);
for (int j = 0; j < valuesList.size(); j++) {
LinkedHashMap<String, String> vmap = valuesList.get(j);
XSSFRow row = sheet.createRow((short) j + 1);
int k = 0;
for (Entry<String, String> entry : vmap.entrySet()) {
XSSFCell cell = row.createCell(k, 0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getValue());
// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
// sheet.autoSizeColumn(k); // 自动调整宽度
k++;
}
}
// System.out.println("############ EXCEL数据填充完成：" + org.mx.travel.util.DateUtil.getDateTime());
  
// 清空response
response.reset();
// 设置response的Header
title += ".xlsx";
response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes("utf-8"), "ISO8859-1" ));
response.setHeader("content-type", "text/html;charset=UTF-8");
response.setContentType("application/octet-stream");
OutputStream out = response.getOutputStream();
workBook.write(out);
out.flush();
out.close();
result = "0";
} catch (Exception e) {
e.printStackTrace();
result = "-1";
}
return result;
}


/**
* Excel 2003
*/
// private final static String XLS = "xls";
/**
* Excel 2007
*/
// private final static String XLSX = "xlsx";
/**
* 分隔符
*/
private final static String SEPARATOR = "/";


/**
* 由Excel文件的Sheet导出至List
* 
* @param file
* @param sheetNum
* @return
*/
public static List<String> exportListFromExcel(File file, int sheetNum) throws IOException {
return exportListFromExcel(new FileInputStream(file), sheetNum);
}
//新增方法：由Excel流的Sheet导出至List（自定义分隔符）
public static List<String> exportListFromExcel(File file, int sheetNum ,String separator) throws IOException {
return exportListFromExcel(new FileInputStream(file), sheetNum ,separator);
}


/**
* 由Excel流的Sheet导出至List
* 
* @param is
* @param extensionName
* @param sheetNum
* @return
* @throws IOException
*/
public static List<String> exportListFromExcel(InputStream is, int sheetNum) throws IOException {


Workbook workbook = null;


/*
* if (extensionName.toLowerCase().equals(XLS)) {
* workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(is);
* } else if (extensionName.toLowerCase().equals(XLSX)) {
* workbook = new XSSFWorkbook(is);
* }
*/
workbook = new XSSFWorkbook(is);
return exportListFromExcel(workbook, sheetNum);
}

//新增方法：由Excel流的Sheet导出至List（自定义分隔符）
public static List<String> exportListFromExcel(InputStream is, int sheetNum, String separator) throws IOException {


Workbook workbook = null;

workbook = new XSSFWorkbook(is);
return exportListFromExcel(workbook, sheetNum, separator);
}


/**
* 由指定的Sheet导出至List
* 
* @param workbook
* @param sheetNum
* @return
* @throws IOException
*/
private static List<String> exportListFromExcel(Workbook workbook, int sheetNum) {


Sheet sheet = workbook.getSheetAt(sheetNum);


// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();


List<String> list = new ArrayList<String>();


int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
StringBuilder sb = new StringBuilder();


short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型，需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + cell.getDateCellValue());
} else {
sb.append(SEPARATOR + cellValue.getNumberValue());
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
list.add(sb.toString());
}
return list;
}

//新增方法：由指定的Sheet导出至List（自定义分隔符）
private static List<String> exportListFromExcel(Workbook workbook, int sheetNum, String separator) {


Sheet sheet = workbook.getSheetAt(sheetNum);


// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();


List<String> list = new ArrayList<String>();


int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
StringBuilder sb = new StringBuilder();
if(row==null){
continue;
}
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
sb.append(separator + "");
continue;
}
// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(separator + cellValue.getBooleanValue());//分隔符有所改变，是自定义的
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型，需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(separator + cell.getDateCellValue());//分隔符有所改变，是自定义的
} else {
sb.append(separator + cellValue.getNumberValue());//分隔符有所改变，是自定义的
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(separator + cellValue.getStringValue());//分隔符有所改变，是自定义的
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
list.add(sb.toString());
}
return list;
}
/**
* 生成Excel(2007版本以上) 通过list结果集
* @param valuesList
* @param title
* @param fileName
* @return
*/
public static String createExcelByList(List<LinkedHashMap<String, String>> valuesList, String title, String fileName) {
String Result = "";
try {
// System.out.println("############ 开始创建EXCEL对象：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作薄
XSSFSheet sheet = workBook.createSheet();//创建sheet页
workBook.setSheetName(0, title);// 工作簿名称
XSSFFont font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("yahei");
XSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFRow titleRow = sheet.createRow((short) 0);// 第一行标题
LinkedHashMap<String, String> map = valuesList.get(0);
Integer i = 0;
// System.out.println("############ 开始拼装EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
for (Entry<String, String> entry : map.entrySet()) {
XSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getKey());
i++;
}
// System.out.println("############ 开始填充EXCEL数据：" + org.mx.travel.util.DateUtil.getDateTime());
XSSFCellStyle style = workBook.createCellStyle();// 创建格式
XSSFFont fontBody = workBook.createFont();
fontBody.setFontName("yahei");
style.setFont(fontBody);
for (int j = 0; j < valuesList.size(); j++) {
LinkedHashMap<String, String> vmap = valuesList.get(j);
XSSFRow row = sheet.createRow((short) j + 1);
int k = 0;
for (Entry<String, String> entry : vmap.entrySet()) {
XSSFCell cell = row.createCell(k, 0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getValue());
// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);
// sheet.autoSizeColumn(k); // 自动调整宽度
k++;
}
}
// System.out.println("############ EXCEL数据填充完成：" + org.mx.travel.util.DateUtil.getDateTime());
HttpServletResponse response = ServletActionContext.getResponse();
response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(fileName+".xls", "utf-8"));
response.setContentType("application/vnd.ms-excel;charset=gb2312");
ServletOutputStream out = response.getOutputStream();
workBook.write(out);
out.flush();
out.close();
Result = "0";
} catch (Exception e) {
e.printStackTrace();
Result = "-1";
}
return Result;
}

/**
* 
* @Method:冻结列头的excel
* @author shiyun
* @date 2016-11-4 上午10:04:01 
* @return String
*/
@SuppressWarnings("static-access")
public static String createAndDownloadExcel(String JsonData, String title, HttpServletResponse response) {
List<LinkedHashMap<String, String>> valuesList = JSON.parseObject(JsonData, new TypeReference<List<LinkedHashMap<String, String>>>() {});
String result = "";
try {

XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作薄
XSSFSheet sheet = workBook.createSheet();
sheet.createFreezePane(0, 3, 0, 0);// 冻结窗口样式
workBook.setSheetName(0, title);// 工作簿名称
XSSFFont font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("yahei");
XSSFCellStyle cellStyle = workBook.createCellStyle();// 创建格式
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 创建提示行
            String tips = " 特别提示：1 以下呈现列（A-G）不可修改编辑，数据均由系统自动填充。如有舱房数据暂未涉及或不可提交，请手动删除行！2 舱房价格暂未确定或没有时，请填写 0 。";
           
            XSSFFont fontTips = workBook.createFont();
            fontTips.setColor(XSSFFont.COLOR_RED);
            fontTips.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            fontTips.setFontName("yahei");
            
            XSSFCellStyle cellTipsStyle = workBook.createCellStyle();// 创建格式
            cellTipsStyle.setFont(fontTips);
            cellTipsStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellTipsStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cellTipsStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellTipsStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            // 设置单元格边框样式     
       cellTipsStyle.setBorderBottom(cellTipsStyle.BORDER_MEDIUM);     
     
            // 设置单元格边框颜色     
            cellTipsStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.GRAY));     
            
            // 提示行
            XSSFRow tipsRow = sheet.createRow((short) 0);// 第一行提示
            tipsRow.setHeightInPoints(30);
            
            for(int t=0; t<28; t++){
            XSSFCell cellTipsData = tipsRow.createCell(t, 0);
            cellTipsData.setCellType(XSSFCell.CELL_TYPE_STRING);
            cellTipsData.setCellStyle(cellTipsStyle);
                 sheet.autoSizeColumn(t); 
            }
            
            XSSFCell cellTips = tipsRow.createCell(0, 0);
            cellTips.setCellType(XSSFCell.CELL_TYPE_STRING);
            cellTips.setCellValue(tips);
            cellTips.setCellStyle(cellTipsStyle);
            sheet.autoSizeColumn(0);

// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 13);// 参数都是从O开始   
       sheet.addMergedRegion(region);
       CellRangeAddress regionB = new CellRangeAddress(0, 0, 14, 27);// 参数都是从O开始   
       sheet.addMergedRegion(regionB); 
            
       // 标题行
       XSSFCellStyle cellTitleStyle = workBook.createCellStyle();// 创建格式
       cellTitleStyle.setFont(font);
       cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellTitleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);

            // 设置单元格边框样式     
       cellTitleStyle.setBorderBottom(cellTipsStyle.BORDER_MEDIUM);    
       cellTitleStyle.setBorderLeft(cellTipsStyle.BORDER_MEDIUM);     
       cellTitleStyle.setBorderRight(cellTipsStyle.BORDER_MEDIUM);     


            // 设置单元格边框颜色     
       cellTitleStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.GRAY));     
       cellTitleStyle.setTopBorderColor(new XSSFColor(java.awt.Color.GRAY));     
       cellTitleStyle.setLeftBorderColor(new XSSFColor(java.awt.Color.GRAY));     
       cellTitleStyle.setRightBorderColor(new XSSFColor(java.awt.Color.GRAY));
            
XSSFRow titleRow = sheet.createRow((short) 1);// 第二行标题
titleRow.setHeightInPoints(20);
LinkedHashMap<String, String> map = valuesList.get(0);
Integer i = 0;

for (Entry<String, String> entry : map.entrySet()) {
XSSFCell cell = titleRow.createCell(i, 0);
cell.setCellStyle(cellTitleStyle);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getKey());
i++;
}

XSSFCellStyle style = workBook.createCellStyle();// 创建格式
XSSFFont fontBody = workBook.createFont();
fontBody.setFontName("yahei");
style.setFont(fontBody);
for (int j = 0; j < valuesList.size(); j++) {
LinkedHashMap<String, String> vmap = valuesList.get(j);
XSSFRow row = sheet.createRow((short) j + 2);
row.setHeightInPoints(20);
int k = 0;
for (Entry<String, String> entry : vmap.entrySet()) {
XSSFCell cell = row.createCell(k, 0);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(entry.getValue());
cell.setCellStyle(style);
sheet.autoSizeColumn(k); // 自动调整宽度
k++;
}
}
// 清空response
response.reset();
// 设置response的Header
title += ".xlsx";
response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes("utf-8"), "ISO8859-1" ));
response.setHeader("content-type", "text/html;charset=UTF-8");
response.setContentType("application/octet-stream");
OutputStream out = response.getOutputStream();
workBook.write(out);
out.flush();
out.close();
result = "0";
}catch(IndexOutOfBoundsException e) {
result = "-2";
}catch (Exception e) {
e.printStackTrace();
result = "-1";
}
return result;
}

}

==============================================

package com.mx.travel.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;


/**
 * Excel导入，兼容2003，2007版本
 * @author lxr
 *
 */
public class ExcelReadUtil {




/**
* 读取Excel,判断文件版本
* @param path
* @return
* @throws IOException
*/
public static  List<String[]> readExcel(String path) throws IOException {
List<String[]> list=new ArrayList();
String postfix = preReadCheck(path);
if (!"".equals(postfix)) {
if ("xls".equals(postfix)) {
list= read2003(path);
} else if ("xlsx".equals(postfix)) {
list= read2007(path);
}
} 
return list;
}



/**
* 校验EXCEL
* @param filePath
* @return
* @throws FileNotFoundException
* @throws FileFormatException
*/
private static String preReadCheck(String filePath) throws FileNotFoundException,
FileFormatException {
String aa = "";
// 常规检查
File file = new File(filePath);
if (!file.exists()) {
throw new FileNotFoundException("传入的文件不存在：" + filePath);
}


if (!(filePath.endsWith("xls") || filePath.endsWith("xlsx"))) {
throw new FileFormatException("传入的文件不是excel");
}

if (filePath.contains(".")) {
aa = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
}
return aa;

}




/**
* 读取excel2007
* @param path
* @return
* @throws IOException
*/
public static List<String[]> read2007(String path) throws IOException {
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
List<String[]> list = new ArrayList<String[]>();


// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 3; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
int firstColumnIndex = xssfRow.getFirstCellNum(); // 首列
int lastColumnIndex = xssfRow.getLastCellNum();// 最后一列
String[] filed= new String[lastColumnIndex];
int vflag=0;
for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
XSSFCell currentCell = xssfRow.getCell(columnIndex);// 当前单元格
String value ="";
if(currentCell!=null){
value = getValue(currentCell);// 当前单元格的值
filed[columnIndex]=value;
if(!value.isEmpty()){
vflag++;
}
}
}
if(vflag>0){
list.add(filed);
}
}
}
}
return list;
}




/**
* 读取excel2003
* @param path
* @return
* @throws IOException
*/
public static List<String[]> read2003(String path) throws IOException {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// Read the Sheet
List<String[]> list = new ArrayList<String[]>();

for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 3; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {//行
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
int firstColumnIndex = hssfRow.getFirstCellNum(); // 首列
int lastColumnIndex = hssfRow.getLastCellNum();// 最后一列
String[] filed= new String[lastColumnIndex];
int vflag=0;


for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {//列
HSSFCell currentCell = hssfRow.getCell((short) columnIndex);// 当前单元格
String value ="";
if(currentCell!=null){
value = getValue(currentCell);// 当前单元格的值
// ss=ss+value+"@@@";
filed[columnIndex]=value;
if(!value.isEmpty()){
vflag++;
}
}
}
//ss=ss+"&&&&";
if(vflag>0){
list.add(filed);
} }
}
//list1.add(ss);
}
return list;
}



/**
* 2007读取单元格
* @param xssfRow
* @return
*/
@SuppressWarnings("static-access")
private static String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}


/**
* 2003读取单元格
* @param hssfCell
* @return
*/
@SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}







}