现在我们定义好对象了,如何导出Excel --ExcelExportUtil 这个导出工具类
public void testExportExcel_1() throws Exception {
ExportParams params = new ExportParams("0328课程表", "日期：2016-03-28", "六年一班")
Workbook workbook = ExcelExportUtil.exportExcel(params, CourseEntity.class,courseList)
FileOutputStream fos = new FileOutputStream("D:/excel/0328课程表.xls")
workbook.write(fos)
fos.close()
}
我们只要把我们定义好的对象的class传进去,以及对象的集合,Easypoi就可以返回一个Excel的workbook了,同时Easypoi是兼容03版本office和07版本office,你要穿个参数指定下类型及可以了,是不是不是比我们自己写代码简单多了,最少只需要2行代码就可以完成我们的office操作了
导入
我们把导出写完了,导入是不是很复杂呢,也不是,导入也是同样简单,定时实体和上面定义的方式一样
导入是用导入工具类
ImportParams params = new ImportParams()
params.setHeadRows(2)
List<CourseEntity>list = ExcelImportUtil.importExcel(inputStream, CourseEntity.class, params)
定义下表头的参数,然后把流传入进去就可以得到我们的对象列表是不是so easy.赶快来使用吧
public class ExcelExport {/**
* 默认每个sheet页最多显示的行数
*/
private static final int sheet_rows = 50000
/**
* 导出Excel文件
*
* @param titleList
* 表头信息
* @param dataList
* 表格数据
* @param fileName
* 导出文件完整名称 demo.xls
* @param request
* @param response
* @throws IOException
*/
public static void exportExcelFile(List<String> titleList,
List<List<String>> dataList, String fileName,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
HSSFWorkbook workBook = exportDataToExcel(titleList, dataList)
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response.setCharacterEncoding("UTF-8")
fileName = encodeFilename(fileName, request)
response.setHeader("Content-disposition", "attachmentfilename=" + fileName)
workBook.write(response.getOutputStream())
response.getOutputStream().flush()
response.getOutputStream().close()
}
public static String encodeFilename(String filename, HttpServletRequest request) {
String agent = request.getHeader("USER-AGENT")
try {
if ((agent != null) && ( 0 <= agent.indexOf("Firefox"))) {
return MimeUtility.encodeText(filename, "UTF-8", "B")
} else if((agent != null) && ( 0 <= agent.indexOf("Chrome"))){
return filename = new String(filename.getBytes(), "ISO8859-1")
}
else {
if (agent != null) {
String newFileName = URLEncoder.encode(filename, "UTF-8")
newFileName = StringUtils.replace(newFileName, "+", "%20")
if (newFileName.length() > 150) {
newFileName = new String(filename.getBytes("GB2312"),
"ISO8859-1")
newFileName = StringUtils.replace(newFileName, " ",
"%20")
}
return newFileName
}
}
} catch (Exception ex) {
return filename
}
return filename
}
public static HSSFWorkbook exportDataToExcel(List<String> titleList, List<List<String>> dataList) {
/* 1.创建一个Excel文件 */
HSSFWorkbook workbook = new HSSFWorkbook()
/* 2.创建Excel的一个Sheet */
HSSFSheet sheet = workbook.createSheet()
/* 3.创建表头冻结 */
sheet.createFreezePane(0, 1)
/* 4.设置列宽 */
for (int i = 0 i < titleList.size() i++) {
sheet.setColumnWidth(i, 5000)
}
/* 5.表头字体 */
HSSFFont headfont = workbook.createFont()
headfont.setFontName("宋体")
headfont.setFontHeightInPoints((short) 12)// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)// 加粗
/* 6.表头样式 */
HSSFCellStyle headstyle = workbook.createCellStyle()
headstyle.setFont(headfont)
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中
// 设置背景色为蓝色
headstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index)
headstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
/* 7.普通单元格字体 */
HSSFFont font = workbook.createFont()
font.setFontName("宋体")
font.setFontHeightInPoints((short) 12)
/* 8.普通单元格样式 */
HSSFCellStyle style = workbook.createCellStyle()
style.setFont(font)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中
/* 9. 拼装表头 */
Iterator<String> titleRowIterator = titleList.iterator()
int columnIndex = 0
HSSFRow row = sheet.createRow(0)
while (titleRowIterator.hasNext()) {
String cellValue = titleRowIterator.next()
HSSFCell cell = row.createCell(columnIndex)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(cellValue)
cell.setCellStyle(headstyle)
columnIndex++
cell = null
}
/* 10.组织表数据 */
Iterator<List<String>> rowIterator = dataList.iterator()
int rowIndex = 1
while (rowIterator.hasNext()) {
List<String> columnList = rowIterator.next()
row = sheet.createRow(rowIndex)
Iterator<String> columnIterator = columnList.iterator()
columnIndex = 0
while (columnIterator.hasNext()) {
String cellValue = columnIterator.next()
HSSFCell cell = row.createCell(columnIndex)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(cellValue)
cell.setCellStyle(style)
cell = null
columnIndex++
}
row = null
rowIndex++
}
return workbook
}
/**
* 重载导出数据到Excel中
* @param titleList 表头
* @param dataList 表数据
* @param amount 每个sheet页显示行数
* @return
*/
public static HSSFWorkbook exportDataToExcel(List<String> titleList, List<List<String>> dataList, int amount) {
/* 1.创建一个Excel文件 */
HSSFWorkbook workbook = new HSSFWorkbook()
//校验传入的参数
if(titleList==null){
titleList = new ArrayList<String>()
}
//无数据直接返回
if(dataList==null || dataList.size()==0){
return workbook
}
//传入数据不正确，按照默认条数显示
if(amount>65535 || amount<=0){
amount = sheet_rows
}
//获取sheet页的数量
int row_num = 0
int y = dataList.size()%amount
if(y == 0){
row_num = dataList.size()/amount
}else{
row_num = dataList.size()/amount + 1
}
/* 表头字体 */
HSSFFont headfont = workbook.createFont()
headfont.setFontName("宋体")
headfont.setFontHeightInPoints((short) 12)// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)// 加粗
/* 表头样式 */
HSSFCellStyle headstyle = workbook.createCellStyle()
headstyle.setFont(headfont)
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中
// 设置背景色为蓝色
headstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index)
headstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
/* 普通单元格字体 */
HSSFFont font = workbook.createFont()
font.setFontName("宋体")
font.setFontHeightInPoints((short) 12)
/* 普通单元格样式 */
HSSFCellStyle style = workbook.createCellStyle()
style.setFont(font)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中
//循环写入每个sheet页
for(int i=0i<row_numi++){
/* 创建Excel的一个Sheet */
HSSFSheet sheet = workbook.createSheet()
/* 创建表头冻结 */
sheet.createFreezePane(0, 1)
/* 设置列宽 */
for (int t = 0 t < titleList.size() t++) {
sheet.setColumnWidth(t, 5000)
}
/* 拼装表头 */
Iterator<String> titleRowIterator = titleList.iterator()
int columnIndex = 0
HSSFRow row = sheet.createRow(0)
while (titleRowIterator.hasNext()) {
String cellValue = titleRowIterator.next()
HSSFCell cell = row.createCell(columnIndex)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(cellValue)
cell.setCellStyle(headstyle)
columnIndex++
cell = null
}
/* 组织表数据 */
int rowIndex = 1
for (int j=amount*i(j<amount*(i+1)&&j<dataList.size())j++) {
List<String> columnList = dataList.get(j)
row = sheet.createRow(rowIndex)
Iterator<String> columnIterator = columnList.iterator()
columnIndex = 0
while (columnIterator.hasNext()) {
String cellValue = columnIterator.next()
HSSFCell cell = row.createCell(columnIndex)
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
cell.setCellValue(cellValue)
cell.setCellStyle(style)
cell = null
columnIndex++
}
row = null
rowIndex++
}
}
return workbook
}
/**
* 重载导出Excel功能,新增一项amount每个sheet导出记录行数
* @param titleList
* @param dataList
* @param fileName
* @param amount 行数如果小于等于0或大于65535则按照默认显示
* @param request
* @param response
* @throws IOException
*/
public static void exportExcelFile(List<String> titleList,
List<List<String>> dataList, String fileName, int amount,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
HSSFWorkbook workBook = exportDataToExcel(titleList, dataList, amount)
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response.setCharacterEncoding("UTF-8")
fileName = encodeFilename(fileName, request)
response.setHeader("Content-disposition", "attachmentfilename=" + fileName)
workBook.write(response.getOutputStream())
response.getOutputStream().flush()
response.getOutputStream().close()
}
}
说明：main方法里的第一个参数为要导出的表头信息，
第二个参数为数据信息，
第三个参数设置文件名，设置好以后直接调用就可以导出Excle了
导入就是把文件存到服务器，可用Mogodb等数据库存储，以下是读取已存根据存储的文件id解析Excle数据的大致过程：
POIFSFileSystem fs = null
if (!StringUtils.isBlank(fileId)) {
fs = new POIFSFileSystem(new ByteArrayInputStream(
//传入对应的文件对象
MongoFileUtil.readFile(fileId)))
}
// 构造 XSSFWorkbook 对象，filePath 传入文件路径
HSSFWorkbook xwb = new HSSFWorkbook(fs)
// 读取第一个sheet
HSSFSheet sheet = xwb.getSheetAt(0)
for (int i = sheet.getFirstRowNum() + 1 i < sheet.getPhysicalNumberOfRows()i++){
//解析每行的数据
HSSFRow singleRow = sheet.getRow(i)
//解析每个单元格数据
singleRow.getCell(0).setCellType(Cell.CELL_TYPE_STRING)
String cellValue= singleRow.getCell(0).getStringCellValue()
}
/*** @author liuwu
* Excel的导入与导出
*/
@SuppressWarnings({ "unchecked" })
public class ExcelOperate {
/**
* @author liuwu
* 这是一个通用的方法，利用了JAVA的反射机制,
* 可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。
* 此方法支持的 javabean属性【数据类型有java基本数据类型及String,Date,byte[](图片转成字节码)】
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
* @throws IOException
*/
public static void exportExcel(String title, String[] headers,Collection<?> dataset, OutputStream out, String pattern) throws IOException {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook()
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title)
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20)
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle()
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index)
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style.setBorderRight(HSSFCellStyle.BORDER_THIN)
style.setBorderTop(HSSFCellStyle.BORDER_THIN)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
// 生成一个字体
HSSFFont font = workbook.createFont()
font.setColor(HSSFColor.VIOLET.index)
font.setFontHeightInPoints((short) 12)
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
// 把字体应用到当前的样式
style.setFont(font)
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle()
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index)
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style2.setBorderRight(HSSFCellStyle.BORDER_THIN)
style2.setBorderTop(HSSFCellStyle.BORDER_THIN)
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER)
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
// 生成另一个字体
HSSFFont font2 = workbook.createFont()
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL)
// 把字体应用到当前的样式
style2.setFont(font2)
// 产生表格标题行
HSSFRow row = sheet.createRow(0)
for (short i = 0 i < headers.length i++) {
HSSFCell cell = row.createCell(i)
cell.setCellStyle(style)
HSSFRichTextString text = new HSSFRichTextString(headers[i])
cell.setCellValue(text)
}
// 遍历集合数据，产生数据行
Iterator<?> it = dataset.iterator()
int index = 0
while (it.hasNext()) {
index++
row = sheet.createRow(index)
Object t = it.next()
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields()
for (short i = 0 i < fields.length i++) {
HSSFCell cell = row.createCell(i)
cell.setCellStyle(style2)
Field field = fields[i]
String fieldName = field.getName()
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1)//注意 实体get Set不要自己改名字不然反射会有问题
try {
Class tCls = t.getClass()
Method getMethod = tCls.getMethod(getMethodName,new Class[] {})
Object value = getMethod.invoke(t, new Object[] {})
HSSFRichTextString richString = new HSSFRichTextString(value.toString())
HSSFFont font3 = workbook.createFont()
font3.setColor(HSSFColor.BLUE.index)
richString.applyFont(font3)
cell.setCellValue(richString)
} catch (SecurityException e) {
e.printStackTrace()
e=null
} catch (NoSuchMethodException e) {
e.printStackTrace()
e=null
} catch (IllegalArgumentException e) {
e.printStackTrace()
e=null
} catch (IllegalAccessException e) {
e.printStackTrace()
e=null
} catch (InvocationTargetException e) {
e.printStackTrace()
e=null
} finally {
// 清理资源
}
}
}
try {
workbook.write(out)
} catch (IOException e) {
e.printStackTrace()
e=null
}
}
}
