POI导出Excel
POI是一款很好用的api,下面这个方法是供前台页面调用，供用户选择存储位置的：
/**
*
* <p>
* Description: excel导出
* </p>
*
* @param request 请求
* @param response 响应
* @param fileName 文件名
* @param columnTitles 列头名
* @param columnNames 列英文名
* @param columnFormats 列类型
* @param sheetName 工作簿
* @param listData 数据集合
* @throws IOException 输入异常
*/
@SuppressWarnings({ "deprecation" , "unused" })
public static void export(HttpServletRequest request, HttpServletResponse response, String fileName,
String[] columnTitles, String[] columnNames, String[] columnFormats, String sheetName,
List<Map<String, Object>> listData) throws IOException {
//创建一个新的Excel
HSSFWorkbook workBook;
workBook = new HSSFWorkbook();
// 生成一个表头样式
HSSFCellStyle styleTitle;
styleTitle = workBook.createCellStyle();
styleTitle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleTitle.setWrapText( true);
/*
* HSSFFont font; font = workBook.createFont();
* font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
* font.setFontHeight((short) WZDX); // 设置字体大小 font.setFontName("宋体");
* // 设置单元格字体 styleTitle.setFont(font);
*/
//创建列的样式
HSSFCellStyle styleCell;
styleCell = workBook.createCellStyle();
styleCell.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCell.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCell.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleCell.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCell.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCell.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleCell.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCell.setWrapText( true);
//创建列的样式
HSSFCellStyle styleCellNumber;
styleCellNumber = workBook.createCellStyle();
styleCellNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCellNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCellNumber.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleCellNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCellNumber.setDataFormat(HSSFDataFormat.getBuiltinFormat( "0"));
//创建列的样式
HSSFCellStyle styleCellDouble;
styleCellDouble = workBook.createCellStyle();
styleCellDouble.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCellDouble.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCellDouble.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleCellDouble.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCellDouble.setWrapText( true);
styleCellDouble.setDataFormat(HSSFDataFormat.getBuiltinFormat( "0.00"));
//创建sheet页
HSSFSheet sheet;
sheet = workBook.createSheet();
//每一列的format如果没有被设置，默认全部为string
String[] columnFormatArr = null;
if (columnFormats == null) {
columnFormatArr = new String[columnNames.length ];
for (int i = 0; i < columnFormatArr.length; i++) {
columnFormatArr[i] = COLUMN_FORMAT_STRING ;
}
//否则按照配置的读取
} else {
columnFormatArr = columnFormats;
}
//sheet页名称 如果没有进行传送 则为默认的工作簿名称
if (sheetName != null && sheetName.length() > 0) {
workBook.setSheetName(0, sheetName);
} else {
workBook.setSheetName(0, SHEET_NAME);
}
//给表的列头赋值
HSSFHeader header;
header = sheet.getHeader();
HSSFRow row; //第一行
row = sheet.createRow(0); //设置第一行为Header
// 设置表格默认列宽度为13个字节
sheet. setDefaultColumnWidth((short) THIRTEENTH);
sheet.setDefaultRowHeightInPoints(( short) TWENTY);
//动态添加列头
for (int i = 0; i < columnTitles.length; i++) {
HSSFCell cell = null;
cell = row. createCell(Short.valueOf(i + ""));
cell.setCellValue(columnTitles[i]);
cell.setCellStyle(styleTitle);
}
if (null != listData) { //数据为空是 则不用填充数据
// 填充excel数据
for (int i = 0; i < listData.size(); i++) {
final int START_ROW = 1;
HSSFRow rows;
rows = sheet.createRow(START_ROW + i);
for (int j = 0; j < columnNames.length; j++) {
HSSFCell cells = null;
cells = rows.createCell(Short.valueOf(j + "" ));
Object columnObject;
columnObject = listData.get(i).get(columnNames[j]);
String columnValue = null;
if (columnObject == null) {
columnValue = "";
} else if (COLUMN_FORMAT_STRING .equals(columnFormatArr[j].toUpperCase())) {
columnValue = columnObject.toString();
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DICT )) {
/*
* String rootKey; 此处是字典 设置 rootKey =
* columnFormatArr[j].substring
* (COLUMN_FORMAT_DICT.length()); columnValue =
* columnObject.toString(); columnValue =
* dictService.findDictValueByDictKey(rootKey,
* columnValue);
*/
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DATE )) {
/*
* CellStyle cellStyle = workBook.createCellStyle();
* DataFormat format= workBook.createDataFormat();
* cellStyle
* .setDataFormat(format.getFormat("yyyy-MM- dd"));
* cell.setCellStyle(cellStyle);
*/
String dateFormat;
dateFormat = columnFormatArr[j].substring(COLUMN_FORMAT_DATE .length());
SimpleDateFormat sdf;
sdf = new SimpleDateFormat(dateFormat);
columnValue = sdf.format(columnObject);
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_NUMBER )) {
cells.setCellType(Cell.CELL_TYPE_NUMERIC );
cells.setCellValue(Integer.parseInt(columnObject.toString()));
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCellNumber);
continue;
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DOUBLE )) {
cells.setCellType(Cell.CELL_TYPE_NUMERIC );
cells.setCellValue(Double.parseDouble(columnObject.toString()));
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCellDouble);
continue;
}
cells.setCellValue(columnValue);
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCell);
}
}
}
response.setContentType("application/vnd.ms-excel" );
//response.setContentType(contentType);
response.setHeader( CONTENT_DISPOSITION, ATTACHMENT
+ new String((fileName).getBytes(GBK ), "iso8859-1") + ".xls");
response.setCharacterEncoding( ISO8859);
OutputStream ouputStream;
ouputStream = response.getOutputStream();
workBook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
对此方法稍作修改，可以改成后台方法生成excel到指定的路径下面：
/**
*
* <p>
* Description: excel导出 供后台任务生成excel 调用
* </p>
*
* @param fileName 文件名
* @param columnTitles 列头名
* @param columnNames 列英文名
* @param columnFormats 列类型
* @param sheetName 工作簿
* @param listData 数据集合
* @throws IOException 输入异常
*/
@SuppressWarnings({ "unused" })
public static void exportToSpecifiedPath(String fileName,
String[] columnTitles, String[] columnNames, String[] columnFormats, String sheetName,
List<Map<String, Object>> listData) throws IOException {
//创建一个新的Excel
XSSFWorkbook workBook;
workBook = new XSSFWorkbook();
XSSFFont font;
font = workBook.createFont();
font.setFontHeightInPoints(( short) TEN );
// 生成一个表头样式
XSSFCellStyle styleTitle;
styleTitle = workBook.createCellStyle();
styleTitle.setFillForegroundColor( new XSSFColor(new Color(0, BLUE_TWO, BLUE_THREE)));
styleTitle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
styleTitle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(XSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleTitle.setWrapText( true);
/*
* HSSFFont font; font = workBook.createFont();
* font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
* font.setFontHeight((short) WZDX); // 设置字体大小 font.setFontName("宋体");
* // 设置单元格字体 styleTitle.setFont(font);
*/
styleTitle.setFont(font);
//创建列的样式
XSSFCellStyle styleCell;
styleCell = workBook.createCellStyle();
styleCell.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleCell.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleCell.setBorderRight(XSSFCellStyle.BORDER_THIN);
styleCell.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleCell.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleCell.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleCell.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCell.setWrapText( true);
styleCell.setFont(font);
//创建列的样式
XSSFCellStyle styleCellNumber;
styleCellNumber = workBook.createCellStyle();
styleCellNumber.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderRight(XSSFCellStyle.BORDER_THIN);
styleCellNumber.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleCellNumber.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleCellNumber.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleCellNumber.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCellNumber.setDataFormat(workBook.createDataFormat().getFormat("0"));
styleCellNumber.setFont(font);
//创建列的样式
XSSFCellStyle styleCellDouble;
styleCellDouble = workBook.createCellStyle();
styleCellDouble.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderRight(XSSFCellStyle.BORDER_THIN);
styleCellDouble.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleCellDouble.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleCellDouble.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleCellDouble.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平布局：居中
styleCellDouble.setWrapText( true);
styleCellDouble.setDataFormat(workBook.createDataFormat().getFormat("0.00"));
styleCellDouble.setFont(font);
//创建sheet页
XSSFSheet sheet;
sheet = workBook.createSheet();
//每一列的format如果没有被设置，默认全部为string
String[] columnFormatArr = null;
if (columnFormats == null) {
columnFormatArr = new String[columnNames.length ];
for (int i = 0; i < columnFormatArr.length; i++) {
columnFormatArr[i] = COLUMN_FORMAT_STRING ;
}
//否则按照配置的读取
} else {
columnFormatArr = columnFormats;
}
//sheet页名称 如果没有进行传送 则为默认的工作簿名称
if (sheetName != null && sheetName.length() > 0) {
workBook.setSheetName(0, sheetName);
} else {
workBook.setSheetName(0, SHEET_NAME);
}
//给表的列头赋值
Header header;
header = sheet.getHeader();
XSSFRow row; //第一行
row = sheet.createRow(0); //设置第一行为Header
// 设置表格默认列宽度为13个字节
sheet.setDefaultColumnWidth(( short) THIRTEENTH);
sheet.setDefaultRowHeightInPoints(( short) TWENTY);
//动态添加列头
for (int i = 0; i < columnTitles.length; i++) {
XSSFCell cell = null;
cell = row.createCell(Short. valueOf(i + ""));
cell.setCellValue(columnTitles[i]);
cell.setCellStyle(styleTitle);
}
if (null != listData) { //数据为空是 则不用填充数据
// 填充excel数据
for (int i = 0; i < listData.size(); i++) {
final int START_ROW = 1;
XSSFRow rows;
rows = sheet.createRow(START_ROW + i);
for (int j = 0; j < columnNames.length; j++) {
XSSFCell cells = null;
cells = rows.createCell(Short.valueOf(j + ""));
Object columnObject;
columnObject = listData.get(i).get(columnNames[j]);
String columnValue = null;
if (columnObject == null) {
columnValue = "";
} else if (COLUMN_FORMAT_STRING .equals(columnFormatArr[j].toUpperCase())) {
columnValue = columnObject.toString();
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DICT )) {
/*
* String rootKey; 此处是字典 设置 rootKey =
* columnFormatArr[j].substring
* (COLUMN_FORMAT_DICT.length()); columnValue =
* columnObject.toString(); columnValue =
* dictService.findDictValueByDictKey(rootKey,
* columnValue);
*/
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DATE )) {
/*
* CellStyle cellStyle = workBook.createCellStyle();
* DataFormat format= workBook.createDataFormat();
* cellStyle
* .setDataFormat(format.getFormat("yyyy-MM- dd"));
* cell.setCellStyle(cellStyle);
*/
String dateFormat;
dateFormat = columnFormatArr[j].substring(COLUMN_FORMAT_DATE .length());
SimpleDateFormat sdf;
sdf = new SimpleDateFormat(dateFormat);
columnValue = sdf.format(columnObject);
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_NUMBER )) {
cells.setCellType(Cell.CELL_TYPE_NUMERIC );
cells.setCellValue(Integer.parseInt(columnObject.toString()));
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCellNumber);
continue;
} else if (columnFormatArr[j].startsWith(COLUMN_FORMAT_DOUBLE )) {
cells.setCellType(Cell.CELL_TYPE_NUMERIC );
cells.setCellValue(Double.parseDouble(columnObject.toString()));
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCellDouble);
continue;
}
cells.setCellValue(columnValue);
//sheet.autoSizeColumn((short)columnValue.getBytes().length);
cells.setCellStyle(styleCell);
}
}
}
File file;
file = new File(fileName);
FileOutputStream ouputStream;
ouputStream = new FileOutputStream(file);
workBook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
后面一个方法生成的是xlsx格式，03版本的工作簿支持的条数较少，如果数量比较大，可以选择导出xlsx格式的,主要就是导入的jar不同。
