这篇博客介绍了如何利用Java的Apache POI库来生成Excel表格，包括需要的jar包以及Apache POI的主要功能，如HSSF和XSSF用于处理不同格式的Excel文件。
最近做数据列表导出成Excel表格，借此机会学习了一下。
首先我们需要通过hibernate链接数据库，将数据库字段映射成POJO实体，这里不多做废话。
我们需要用excel表格工具类，首先需要几个poi的jar包，poi读取excel数据所需要的jar包。其他的还需要一些commons-lang等语言包。
poi-3.9-20130828.jar
poi-ooxml-3.9-20130828.jar
poi-ooxml-schemas-3.9-20130828.jar
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。
下面我们来看一下Apache POI 中提供的几大部分的作用：
HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF － 提供读写Microsoft Word DOC格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读Microsoft Visio格式档案的功能。
HPBF － 提供读Microsoft Publisher格式档案的功能。
HSMF － 提供读Microsoft Outlook格式档案的功能。
1.ExcelCreater 创建Excel表格类
package com.yuanding.common.data.excel;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.yuanding.common.data.excel.ExcelFile.ExcelStyle;
import com.yuanding.common.util.Constants;
public class ExcelCreater {
static {
ExcelCreater.setFilePath(Constants.Path.DOC_PATH);
}
private String fullFileName;
private static String filePath;
private String title;
private String sheetTitle;
private String[] columnTitle;
private String[] columnKey;
private String[] columnWidth;
private List<Map<String, Object>> data;
public String getFullFileName() {
return filePath + fullFileName;
}
public void setFullFileName(String fullFileName) {
this.fullFileName = fullFileName;
}
public static String getFilePath() {
return filePath;
}
public static void setFilePath(String filePath) {
File file = new File(filePath);
if(file.exists() && file.isDirectory()){
ExcelCreater.filePath = filePath;
}else{
ExcelCreater.filePath = System.getProperty("java.io.tmpdir");
}
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getColumnTitle() {
return columnTitle;
}
public void setColumnTitle(String[] columnTitle) {
this.columnTitle = columnTitle;
}
public String[] getColumnKey() {
return columnKey;
}
public void setColumnKey(String[] columnKey) {
this.columnKey = columnKey;
}
public String[] getColumnWidth() {
return columnWidth;
}
public void setColumnWidth(String[] columnWidth) {
this.columnWidth = columnWidth;
}
public List<Map<String, Object>> getData() {
return data;
}
public void setData(List<Map<String, Object>> data) {
this.data = data;
}
public String getSheetTitle() {
return sheetTitle;
}
public void setSheetTitle(String sheetTitle) {
this.sheetTitle = sheetTitle;
}
public boolean checkParam() {
return fullFileName != null && columnTitle != null && columnKey != null;
}
public boolean create() {
if (checkParam()) {
ExcelFile excel = new ExcelFile();
Map<String, String> sheetStyle = new HashMap<String, String>();
sheetStyle.put(ExcelStyle.SHEET_HEAD, this.getTitle());
sheetStyle.put(ExcelStyle.TITLE_HEIGHT, "500");
sheetStyle.put(ExcelStyle.ROW_HEIGHT, "500");
List<Map<String, String>> rowStyle = new ArrayList<Map<String, String>>();
for (int i = 0, len = columnTitle.length; i < len; i++) {
Map<String, String> styleItem1 = new HashMap<String, String>();
styleItem1.put(ExcelStyle.CELL_TITLE, this.getColumnTitle()[i]);
styleItem1.put(ExcelStyle.CELL_NAME, this.getColumnKey()[i]);
styleItem1.put(ExcelStyle.CELL_WIDTH, this.getColumnWidth()[i]);
rowStyle.add(styleItem1);
}
List<Map<String, Object>> sheetList = new ArrayList<Map<String, Object>>();
Map<String, Object> sheet1 = new HashMap<String, Object>();
sheet1.put(ExcelStyle.SHEET_LABEL, this.getSheetTitle());
sheet1.put(ExcelStyle.SHEET_STYLE, sheetStyle);
sheet1.put(ExcelStyle.ROW_STYLE, rowStyle);
sheet1.put(ExcelStyle.DATA, this.getData());
sheetList.add(sheet1);
try {
excel.write(filePath + fullFileName, sheetList);
return true;
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
}
2.ExcelFile Excel文件工具类
package com.yuanding.common.data.excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Excel文件工具
* 类名: ExcelFile</br>
* 包名：com.diyvc.common.data.excel </br>
*/
public class ExcelFile {
public class ExcelStyle {
public final static String CELL_NAME = "name";
public final static String CELL_TITLE = "title";
public final static String CELL_WIDTH = "width";
public final static String ROW_STYLE = "row_style";
public final static String ROW_HEIGHT = "row_height";
public final static String SHEET_STYLE = "sheet_style";
public final static String SHEET_LABEL = "sheet_title";
public final static String SHEET_HEAD = "sheet_head";
public final static String TITLE_HEIGHT = "title_height";
public final static String DATA = "data";
}
private Font font;
private CellStyle style;
/*
* 构建标题单元格样式
*/
private CellStyle getSheetTitleStyle(Workbook workbook) {
// 设置表头字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeight((short) 220);
// 设置标题格式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFont(font);// 设置字体
return style;
}
/*
* 构建表头单元格样式
*/
private CellStyle getColumnTitleStyle(Workbook workbook) {
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeight((short) 200);
// 设置标题格式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 设置边框
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);// 设置字体
return style;
}
/*
* 构建一般单元格样式
*/
private CellStyle getCellStyle(Workbook workbook) {
if (style == null) {
// 设置字体
font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 200);
// 设置单元格格式
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
// 设置边框
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);// 设置字体
}
return style;
}
/**
* 创建Excel文件
*
* @param filename Excel文件
* @param title Sheet标题
* @param style Sheet列格式(name: 列名称,与data中数据对应, title：Sheet中显示的标题，width：列宽度)
* @param data Sheet数据
*/
@SuppressWarnings("unchecked")
public void write(String filename, List<Map<String, Object>> sheetList) throws Exception {
Workbook workbook = new XSSFWorkbook();
for (Map<String, Object> sheetMap : sheetList) {
if (sheetMap != null && sheetMap.containsKey(ExcelStyle.SHEET_LABEL) && sheetMap.containsKey(ExcelStyle.ROW_STYLE)) {
String sheetTitle = (String) sheetMap.get(ExcelStyle.SHEET_LABEL);
Map<String, String> sheetStyle = sheetMap.containsKey(ExcelStyle.SHEET_STYLE) ? (Map<String, String>) sheetMap
.get(ExcelStyle.SHEET_STYLE) : new HashMap<String, String>();
List<Map<String, String>> rowStyle = (List<Map<String, String>>) sheetMap.get(ExcelStyle.ROW_STYLE);
List<Map<String, Object>> data = (sheetMap.containsKey(ExcelStyle.DATA)) ? (List<Map<String, Object>>) sheetMap
.get(ExcelStyle.DATA) : new ArrayList<Map<String, Object>>();
Sheet sheet = workbook.createSheet(sheetTitle);
List<String> columnNameList = new ArrayList<String>();
List<String> columnTitleList = new ArrayList<String>();
List<Integer> columnWidthList = new ArrayList<Integer>();
for (Map<String, String> styleItem : rowStyle) {
if (styleItem.containsKey(ExcelStyle.CELL_NAME)) {
columnNameList.add(styleItem.get(ExcelStyle.CELL_NAME));
}
if (styleItem.containsKey(ExcelStyle.CELL_TITLE)) {
columnTitleList.add(styleItem.get(ExcelStyle.CELL_TITLE));
}
if (styleItem.containsKey(ExcelStyle.CELL_WIDTH)) {
String width = styleItem.get(ExcelStyle.CELL_WIDTH);
if (StringUtils.isNumeric(width)) {
columnWidthList.add(Integer.parseInt(width));
}
}
}
createTitle(workbook, sheet, sheetTitle, sheetStyle, columnNameList, columnTitleList, columnWidthList);
//设置行高
short rowHeight = 300;
if (sheetStyle.containsKey(ExcelStyle.ROW_HEIGHT)) {
rowHeight = Short.parseShort(sheetStyle.get(ExcelStyle.ROW_HEIGHT));
}
//设置行内容
for (int i = 0; i < data.size(); i++) {
Map<String, Object> item = data.get(i);
if (item == null) {
continue;
}
//构建内容行(2 包括标题栏和表头)
Row row = sheet.createRow(i + 2);
row.setHeight(rowHeight);
for (int j = 0; j < columnNameList.size(); j++) {
Object value = item.containsKey(columnNameList.get(j)) ? item.get(columnNameList.get(j)) : "";
Cell cell = row.createCell(j);
cell.setCellStyle(getCellStyle(workbook));
if (value instanceof Short || value instanceof Integer || value instanceof Long
|| value instanceof Double) {
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
}
}
}
}
}
OutputStream os = new FileOutputStream(filename);
workbook.write(os);
os.close();
}
/*
* 构建表头
*
* @param sheet 构建的表格
*/
private void createTitle(Workbook workbook, Sheet sheet, String sheetTitle, Map<String, String> sheetStyle, List<String> columnNameList,
List<String> columnTitleList, List<Integer> columnWidthList) {
//设置标题栏高
short titleHeight = 300;
if (sheetStyle.containsKey(ExcelStyle.TITLE_HEIGHT)) {
titleHeight = Short.parseShort(sheetStyle.get(ExcelStyle.TITLE_HEIGHT));
}
//设置标题内容
String sheetHead = sheetTitle;
if (sheetStyle.containsKey(ExcelStyle.SHEET_HEAD)) {
sheetHead = sheetStyle.get(ExcelStyle.SHEET_HEAD);
}
//设置表头高
short rowHeight = 300;
if (sheetStyle.containsKey(ExcelStyle.ROW_HEIGHT)) {
rowHeight = Short.parseShort(sheetStyle.get(ExcelStyle.ROW_HEIGHT));
}
//构建标题栏
Row sheetTitleRow = sheet.createRow(0);
sheetTitleRow.setHeight(titleHeight);
// 合并标题栏
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNameList.size() - 1));
Cell cell = sheetTitleRow.createCell(0);
cell.setCellStyle(getSheetTitleStyle(workbook));
cell.setCellValue(sheetHead);
//构建表头
Row columnTitleRow = sheet.createRow(1);
columnTitleRow.setHeight(rowHeight);
for (int i = 0; i < columnTitleList.size(); i++) {
cell = columnTitleRow.createCell(i);
cell.setCellStyle(getColumnTitleStyle(workbook));
cell.setCellValue(columnTitleList.get(i));
}
for (int i = 0; i < columnWidthList.size(); i++) {
sheet.setColumnWidth(i, columnWidthList.get(i));
}
}
/**
* 读取Excel文件
*
* @param filename Excel文件名
* @param sheetIndex Sheet编号(起始为0)
* @param titleRow 标题列的高度(如果为0，则表明没有标题行)
* @param columns 列名
*
* @return 读取后的Excel数据
*/
public List<Map<String, Object>> read(String filename, int sheetIndex, int titleRow, String[] columns) {
InputStream fileStream = null;
try {
fileStream = new FileInputStream(filename);
if (filename.endsWith("xlsx")) {
return this.read(fileStream, sheetIndex, titleRow, columns);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally{
IOUtils.closeQuietly(fileStream);
}
return null;
}
/**
* 读取Excel文件
*
* @param fileStream Excel文件流
* @param sheetIndex Sheet编号(起始为0)
* @param titleRow 标题列的高度(如果为0，则表明没有标题行)
* @param columns 列名
*
* @return 读取后的Excel数据
*/
public List<Map<String, Object>> read(InputStream fileStream, int sheetIndex, int titleRow, String[] columns) {
List<Map<String, Object>> table = new ArrayList<Map<String, Object>>();
try {
Workbook wb = new XSSFWorkbook(fileStream);
Sheet sheet = wb.getSheetAt(sheetIndex);
int rowCount = 0;
Iterator<Row> rows = sheet.iterator();
while (rows.hasNext()) {
Row row = rows.next();
rowCount++;
//过滤标题行
if (titleRow >= rowCount) {
continue;
}
Map<String, Object> item = new HashMap<String, Object>();
for (int i = 0; i < columns.length; i++) {
item.put(columns[i], getCellValue(row, i));
}
table.add(item);
}
IOUtils.closeQuietly(fileStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(fileStream);
}
return table;
}
private Object getCellValue(Row row, int index) {
Cell cell = row.getCell(index);
Object value = null;
if (cell instanceof XSSFCell) {
XSSFCell xssfCell = (XSSFCell) cell;
if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
if(HSSFDateUtil.isCellDateFormatted(xssfCell)){
value = xssfCell.getDateCellValue();
}else{
value = (int) xssfCell.getNumericCellValue();
}
} else if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
value = xssfCell.getStringCellValue();
}
}
return value;
}
}
3.contants文件配置类
package com.yuanding.common.util;
public class Constants {
public static class Path{
//文件路径
public final static String DOC_PATH = Configure.values("doc_path");
public final static String FILE_PATH = Configure.values("file_path");
public final static String PRODUCT_PATH = Configure.values("product_path");
//IP纯真数据库的文件名 所在文件夹路径 (需修改)IpToAdd.Dat
public static final String IP_ADDRESS_FILE_NAME = "IpToAdd.Dat";
public static final String IP_ADDRESS_FILE_PATH =Configure.values("ip_path");
}
}/**
* 方法名：园区信息-导出
* 详述：导出园区信息数据
* @param response
* @param request 说明参数含义
* @return void说明返回值含义
* @throws 说明发生此异常的条件
*/
@ResponseBody
@RequestMapping(value = "/export",method = {RequestMethod.POST,RequestMethod.GET})
public void exportDataExcel(HttpServletResponse response,HttpServletRequest request){
try{
Map<String, Object> properties = new HashMap<String, Object>();
//园区名称
String parkName = request.getParameter("parkName");
//根据用户名查询
if(StringUtils.isNotBlank(parkName)) {
properties.put("name:like", parkName);
}
List<PakParkInfo> list = parkBiz.findPage(null, properties);
if(list.size()>0){
//组装导出数组
List<Map<String, Object>> eclist = new ArrayList<Map<String,Object>>();
Date date=new Date();
String now=DateUtil.parseDateToStr(date, DateUtil.DATE_TIME_FORMAT_YYYYMMDDHHMISSSSS);
String fileName=now+"园区列表数据.xlsx";
//表格信息
ExcelCreater ec = new ExcelCreater();
ec.setFullFileName(fileName);
ec.setTitle("园区列表数据");
ec.setSheetTitle("sheet1");
ec.setColumnTitle(new String[] { "园区名称", "责任人", "手机号" ,"地址","入驻企业数","创建时间"});//设置表头
ec.setColumnKey(new String[] { "name", "contacts", "cellphone","address","enterpriseNum","createTime"});//设置key
ec.setColumnWidth(new String[] { "5000","5000","5000","15000","3000","10000"});//设置列宽
for(PakParkInfo pakParkInfo :list){
Map<String, Object > ecmap = new HashMap<String, Object>();
ecmap.put("name", pakParkInfo.getName());
ecmap.put("contacts", pakParkInfo.getContacts());
ecmap.put("cellphone", pakParkInfo.getCellphone());
ecmap.put("address", pakParkInfo.getAddress());
ecmap.put("enterpriseNum", pakParkInfo.getEnterpriseInfoList().size());
ecmap.put("createTime", DateUtil.parseDateToStr(pakParkInfo.getCreateTime(), DateUtil.DATE_TIME_FORMAT_YYYY_MM_DD_HH_MI_SS));
eclist.add(ecmap);
}
//设置表格列表信息
ec.setData(eclist);
//判断表格是否创建成功
if(ec.create()){
InputStream fis = null;
byte[] buffer = null;
OutputStream os = null;
try{
fis = new BufferedInputStream(new FileInputStream(ec.getFullFileName()));
buffer = new byte[fis.available()];
fis.read(buffer);
response.reset();
// 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.replaceAll(" ", "").getBytes("utf-8"),"iso8859-1"));
os = response.getOutputStream();
response.setContentType("application/octet-stream");
os.write(buffer);// 输出文件
os.flush();
}catch (Exception e) {
e.printStackTrace();
}finally{
try {
fis.close();
os.close();
System.out.print("导出文件-----------");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}catch(Exception e){
e.printStackTrace();
}
}上面的代码是我们先获取实体，通过对象中的数据拿到我们想要的字段，将数据保存到集合中
待编辑。。。
扫一扫
261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
