在写代码之前将poi导入到我们的项目中
在pom.xml中添加如下依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.9</version>
</dependency>
</dependencies>开始封装我们的工具类，这里的两个参数用于区别不同版本的excel文件
public class ExcelUtil {
private ExcelUtil() {
}
private final String excel2003L = ".xls";//2003- 版本的excel
private final String excel2007U = ".xlsx";//2007+ 版本的excel
public static final ExcelUtil getInstance() {
return ExcelUtilHolder.instance;
}
private static final class ExcelUtilHolder {
private static final ExcelUtil instance = new ExcelUtil();
}
}首先获取到文件的后缀，根据不同的版本创建我们的Workbook
/**
* 根据文件后缀，自适应上传文件的版本
*/
private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {
Workbook workbook;
String fileType = filename.substring(filename.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
workbook = new HSSFWorkbook(inStr);//2003-
} else if (excel2007U.equals(fileType)) {
workbook = new XSSFWorkbook(inStr);//2007+
} else {
throw new Exception("解析的文件格式有误");
}
return workbook;
}创建完Workbook之后，我们通过Workbook这个对象获取excel中的数据。
HSSFWorkbook和XSSFWorkbook 是实现了Workbook接口的Excel的文档对象。
/**
* 获取excel中的数据
*/
public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
List<List<Object>> list;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
//通过getCellValue方法获取当前行每一列中的数据
li.add(getCellValue(cell));
}
//将每一行的数据添加到list
list.add(li);
}
}
in.close();
return list;
}然后获取每个单元格的方法
/**
* 获取每个单元格的内容
*/
private Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}至此我们就已经可以获取到excel表格中的数据了
数据转成excel表格
/**
* 数据转成excel
*
* @param dataList 需要转换的数据
* @param sheetName 生成的excel表名
* @param columnName 每一列的名字(key)
* @return
*/
public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {
int columnNum = columnName.length;
Workbook workbook = new HSSFWorkbook();
//创建第一页，并命名
Sheet sheet = workbook.createSheet(sheetName);
//创建第一行
Row row = sheet.createRow(0);
// 创建两种单元格格式
CellStyle cs = workbook.createCellStyle();
CellStyle cs2 = workbook.createCellStyle();
// 创建两种字体
Font f = workbook.createFont();
Font f2 = workbook.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
//设置列名
for (int i = 0; i < columnNum; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
int rowNum = dataList.size();
for (int j = 1; j <= rowNum; j++) {
//创建一行
Row r = sheet.createRow(j);
for (int k = 0; k < columnNum; k++) {
Cell cell = r.createCell(k);
cell.setCellValue(dataList.get(j - 1).get(columnName[k]));
cell.setCellStyle(cs2);
}
}
return workbook;
}最后在我们的控制器中实现excel文件的下载,dataList中的数据请自行获取或添加
public void downloadExcel(List<Map<String, String>> dataList, HttpServletResponse servletResponse) throws IOException {
String[] columnName = {"name", "location", "manager", "phone", "description", "latitude", "longitude"};
Workbook workbook = ExcelUtil.getInstance().dataToExcel(dataList, "CompanyInfo", columnName);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
servletResponse.reset();
servletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
servletResponse.setHeader("Content-Disposition", "attachment;filename=" + new String(("CompanyInfo" + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = servletResponse.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}下图这是整个ExcelUtil的代码
package Utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Created by Clanner on 2017/5/10.
*/
public class ExcelUtil {
private ExcelUtil() {
}
private final String excel2003L = ".xls";//2003- 版本的excel
private final String excel2007U = ".xlsx";//2007+ 版本的excel
/**
* 根据文件后缀，自适应上传文件的版本
*/
private Workbook getWorkbook(InputStream inStr, String filename) throws Exception {
Workbook workbook;
String fileType = filename.substring(filename.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
workbook = new HSSFWorkbook(inStr);//2003-
} else if (excel2007U.equals(fileType)) {
workbook = new XSSFWorkbook(inStr);//2007+
} else {
throw new Exception("解析的文件格式有误");
}
return workbook;
}
/**
* 获取excel中的数据
*/
public List<List<Object>> readExcelData(InputStream in, String filename) throws Exception {
List<List<Object>> list;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
//通过getCellValue方法获取当前行每一列中的数据
li.add(getCellValue(cell));
}
//将每一行的数据添加到list
list.add(li);
}
}
in.close();
return list;
}
/**
* 获取每个单元格的内容
*/
private Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
/**
* 数据转成excel
*
* @param dataList 需要转换的数据
* @param sheetName 生成的excel表名
* @param columnName 每一列的名字(key)
* @return
*/
public Workbook dataToExcel(List<Map<String, String>> dataList, String sheetName, String[] columnName) {
int columnNum = columnName.length;
Workbook workbook = new HSSFWorkbook();
//创建第一页，并命名
Sheet sheet = workbook.createSheet(sheetName);
//创建第一行
Row row = sheet.createRow(0);
// 创建两种单元格格式
CellStyle cs = workbook.createCellStyle();
CellStyle cs2 = workbook.createCellStyle();
// 创建两种字体
Font f = workbook.createFont();
Font f2 = workbook.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
//设置列名
for (int i = 0; i < columnNum; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
int rowNum = dataList.size();
for (int j = 1; j <= rowNum; j++) {
//创建一行
Row r = sheet.createRow(j);
for (int k = 0; k < columnNum; k++) {
Cell cell = r.createCell(k);
cell.setCellValue(dataList.get(j - 1).get(columnName[k]));
cell.setCellStyle(cs2);
}
}
return workbook;
}
public static final ExcelUtil getInstance() {
return ExcelUtilHolder.instance;
}
private static final class ExcelUtilHolder {
private static final ExcelUtil instance = new ExcelUtil();
}
}
扫一扫
322
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
