java写excel文件工具
之前写过一个用java操作csv与excel文件的工具《操作CSV与EXCEL的工具》，它写入几十、几百行数据都没有问题。但如果写几万行，就有些吃力了，速度太慢。另外也没有抽出工具代码，与业务代码耦合性太强，为此写了下面这个工具。
特点:
1.复用文件流，高效
2.业务代码可定义每一行的数据，与输入多行数据相比自由度更大
3.使用简单，只需调用一个方法
约束:
依赖poi类库
工具只有下面一个类，使用方法如下。
Controller类里添加方法:
@RequestMapping("/export")
public void export(HttpServletRequest req,HttpServletResponse rsp){
//查询时,前端拼接的表头
List<List<String>> rows=业务代码查出数据();
XSSFWorkbook wb= WriteExcel.createNewSheet(rows,paper.getName());
WriteExcel.writeDatas("fileName.xlsx", new WriteExcel.WriteSheet() {
//写一个Sheet
@Override
public void write(Sheet sheet) {
int rowNum=1;
for(List<String> row:rows){
this.writeExcel(sheet, rowNum++, row);
if(rowNum%100==0){
log.info("rowNum:"+rowNum);
}
}
log.info("rowNum:"+rowNum);
}
});
Calendar end=Calendar.getInstance();
log.info("load data time:"+(end.getTimeInMillis()-start.getTimeInMillis())+"ms");
try {
WriteExcel.export(req,rsp,wb,"file","xlsx");
} catch (IOException e) {
log.error(e.getMessage());
}
log.info("total time:"+(Calendar.getInstance().getTimeInMillis()-start.getTimeInMillis())+"ms");
}javascript调用这个方法:
location.href="/export?param1=a&p2=b";
工具源码:
package net.highesoft.ssm.util;
import java.io.*;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class WriteExcel {
private static Log log = LogFactory.getLog(WriteExcel.class);
/**
* 写入文件fileName 一个excel只有一个sheet的情况
*
* @param fileName
* @param wl
*/
public static void writeDatas(String fileName, WriteSheet wl) {
WriteExcel tool = new WriteExcel();
Workbook wb = null;
try {
wb = tool.getWorkbook(fileName);
Sheet sheet = tool.getSheet(wb);
wl.write(sheet);
} catch (Exception e) {
log.error(e.getMessage(), e);
} finally {
try {
tool.close(fileName, wb);
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
}
public static XSSFWorkbook appendDatasNewSheet(XSSFWorkbook wb, WriteSheet wl) {
if (wb == null) {
wb = new XSSFWorkbook();
}
try {
Sheet sheet = wb.createSheet(wl.name);
wl.write(sheet);
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return wb;
}
public static XSSFWorkbook createNewSheet(String sheetName, WriteSheet wl) {
XSSFWorkbook wb = new XSSFWorkbook();
try {
Sheet sheet = wb.createSheet(sheetName);
wl.write(sheet);
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return wb;
}
public static XSSFWorkbook appendDatasNewSheet(XSSFWorkbook wb, String sheetName, WriteSheet wl) {
if (wb == null) {
wb = new XSSFWorkbook();
}
try {
Sheet sheet = wb.createSheet(sheetName);
wl.write(sheet);
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return wb;
}
public static XSSFWorkbook createNewSheet(final JSONObject jsonObj,String name) {
XSSFWorkbook wb=createNewSheet( name, new WriteSheet(name) {
//写一个Sheet
@Override
public void write(Sheet sheet) {
int rowNum=0;
JSONArray jsonArray = jsonObj.getJSONArray(this.getName());
for(int i=0;i<jsonArray.size();i++) {
JSONArray row=jsonArray.getJSONArray(i);
String val[]=new String[row.size()];
this.writeExcel(sheet, rowNum++, Arrays.asList(row.toArray(val)));
}
}
});
return wb;
}
public static XSSFWorkbook createNewSheet(final List<List<String>> rows,String name) {
XSSFWorkbook wb=createNewSheet( name, new WriteSheet(name) {
//写一个Sheet
@Override
public void write(Sheet sheet) {
int rowNum=0;
for(int i=0;i<rows.size();i++) {
List<String> row=rows.get(i);
this.writeExcel(sheet, rowNum++, row);
}
}
});
return wb;
}
public static void writeSheet(final JSONObject jsonObj,XSSFWorkbook wb,String name) {
appendDatasNewSheet(wb, new WriteSheet(name) {
public void write(Sheet sheet) {
int rowNum=0;
JSONArray jsonArray = jsonObj.getJSONArray(this.getName());
for(int i=0;i<jsonArray.size();i++) {
JSONArray row=jsonArray.getJSONArray(i);
String val[]=new String[row.size()];
this.writeExcel(sheet, rowNum++, Arrays.asList(row.toArray(val)));
}
}
});
}
private Workbook getWorkbook(String fileName) throws Exception {
Workbook wb = null;
File file = new File(fileName);
if (fileName.endsWith("xls")) { // Excel 2003
wb = new HSSFWorkbook();
} else if (fileName.endsWith("xlsx")) { // Excel 2007/2010
wb = new XSSFWorkbook();
}else {
log.error("文件名少后缀名，请添加.xlsx");
}
if (!file.exists()) {
file.createNewFile();
}
return wb;
}
private Sheet getSheet(Workbook wb) throws Exception {
// 读取第一章表格内容
Sheet sheet = null;
if (wb.getNumberOfSheets() == 0) {
sheet = wb.createSheet();
} else {
sheet = wb.getSheetAt(0);
}
return sheet;
}
private void close(String fileName, Workbook wb) throws Exception {
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
FileOutputStream out = null;
if (fileName != null) {
out = new FileOutputStream(fileName);
}
if (wb != null) {
wb.write(out);
}
if (out != null) {
out.close();
}
}
public static void export(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook wb, String fileName, String suffix) throws IOException {
String recommendedName;
//判断是否是IE11
Boolean flag = request.getHeader("User-Agent").indexOf("like Gecko") > 0;
//IE11 User-Agent字符串:Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
//IE6~IE10版本的User-Agent字符串:Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.0; Trident/6.0)
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") > 0 || flag) {
recommendedName = URLEncoder.encode(fileName, "UTF-8");//IE浏览器
} else {
//先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,
//这个文件名称用于浏览器的下载框中自动显示的文件名
recommendedName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
//firefox浏览器
//firefox浏览器User-Agent字符串:
//Mozilla/5.0 (Windows NT 6.1; WOW64; rv:36.0) Gecko/20100101 Firefox/36.0
}
request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
if (suffix != null && suffix.equals(".xlsx")) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else {
response.setContentType("application/vnd.ms-excel");
}
response.setHeader("Content-disposition", "attachment;filename=" + recommendedName + (suffix == null ? ".xls" : suffix));
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
public static abstract class WriteSheet {
private String name;
public WriteSheet() {}
public WriteSheet(String name) {
this.name=name;
}
public String getName() {
return name;
}
public abstract void write(Sheet sheet);
// 只支持内容为String类型,一般情况下般都只是给人看，而不能计算
public void writeExcel(Sheet sheet, int rowNum, List<String> cells) {
// 检测代码
try {
Row row = sheet.createRow(rowNum);
for (int i = 0; i < cells.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(String.valueOf(cells.get(i)));
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* 合并某一列的数据，从第一行到末尾，相邻相同的合并，合并单元格的值设为第一行的值
* @param sheet
* @param col
*/
public void mergeCol(Sheet sheet,int col) {
if(sheet!=null) {
int startMerge=0;
int endMerge=0;
String lastCellVal=null;
for (int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
Row row=sheet.getRow(rowNum);
String thisCellVal=row.getCell(col).getStringCellValue();
if(lastCellVal==null) {
lastCellVal=row.getCell(col).getStringCellValue();
startMerge=rowNum;
}else {
if(lastCellVal.equals(thisCellVal)) {
endMerge++;
}else {
if(endMerge-startMerge>0) {
//有可以合并的行,值为合并区域的第一个
/**
* Adds a merged region of cells (hence those cells form one).
*
* @param region (rowfrom/colfrom-rowto/colto) to merge
* @return index of this region
*/
sheet.addMergedRegion(new CellRangeAddress(startMerge, endMerge, col, col));
}
lastCellVal=thisCellVal;
startMerge=rowNum;
endMerge=rowNum;
}
}
}
if(endMerge-startMerge>0) {
//有可以合并的行
sheet.addMergedRegion(new CellRangeAddress(startMerge, endMerge, col, col));
}
}
}
}
}
</pre><br><p><b>写多个Sheet,加合并调用:</b></p><p><b></b></p><pre class="prettyprint lang-java" contenteditable="false">final JSONArray districtGradeLevelAbility=report.getJSONArray("districtGradeLevelAbility");
XSSFWorkbook wb=WriteExcel.appendDatasNewSheet(wb, "各区县学生五大阅读能力基本情况-学段维度", new WriteSheet() {
//写一个Sheet
@Override
public void write(Sheet sheet) {
int rowNum=0;
this.writeExcel(sheet, rowNum++, Arrays.asList("区县","学段","整体感知","获取信息","形成解释","作出评价","创意运用"));
for(Object jobj:districtGradeLevelAbility) {
JSONObject row=JSONObject.parseObject(String.valueOf(jobj));
this.writeExcel(sheet, rowNum++, Arrays.asList(row.getString("districtName"),row.getString("gradeLevelName"),row.getString("globalFeel"),row.getString("getInfo"),row.getString("explain"),row.getString("comment"),row.getString("create")));
}
//合并单元格
this.mergeCol(sheet, 0);
}
});
final JSONArray survey=report.getJSONArray("survey");
wb=WriteExcel.appendDatasNewSheet(wb, "调查问卷题目", new WriteSheet() {
//写一个Sheet
@Override
public void write(Sheet sheet) {
int rowNum=0;
this.writeExcel(sheet, rowNum++, Arrays.asList("题目","学段","A答案数量","B答案数量","C答案数量","D答案数量","E答案数量"));
for(Object jobj:survey) {
JSONObject row=JSONObject.parseObject(String.valueOf(jobj));
this.writeExcel(sheet, rowNum++, Arrays.asList(row.getString("questionName"),row.getString("gradeLevelName"),row.getString("answerA"),row.getString("answerB"),row.getString("answerC"),row.getString("answerD"),row.getString("answerE")));
}
//合并单元格
this.mergeCol(sheet, 0);
}
});有时我们有一个即有列名又有数据的List<list
final JSONObject jsonObj = jsonObject.getJSONObject("data");
XSSFWorkbook wb=WriteExcel.createNewSheet(jsonObj,"整体能力分析");
WriteExcel.writeSheet(jsonObj,wb,"分数段人数");spring mvc 下载方法:
public ResponseEntity downloadUserOrderCoupon(UserCouponCond userCouponCond) throws UnsupportedEncodingException,IOException {
// TODO Auto-generated method stub
String path="";
File file=new File(path);
HttpHeaders headers = new HttpHeaders();
String fileName=new String(file.getName().getBytes("UTF-8"),"iso-8859-1");//为了解决中文名称乱码问题
headers.setContentDispositionFormData("attachment", fileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
return new ResponseEntity(FileUtils.readFileToByteArray(file),
headers, HttpStatus.CREATED);
}这里用了File,在硬盘上会生成一个文件。如果不要生成文件，也可用下面的方法导出:
protected void export(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook wb, String fileName, String suffix) throws IOException {
String recommendedName;
//判断是否是IE11
Boolean flag = request.getHeader("User-Agent").indexOf("like Gecko") > 0;
//IE11 User-Agent字符串:Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
//IE6~IE10版本的User-Agent字符串:Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.0; Trident/6.0)
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") > 0 || flag) {
recommendedName = URLEncoder.encode(fileName, "UTF-8");//IE浏览器
} else {
//先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,
//这个文件名称用于浏览器的下载框中自动显示的文件名
recommendedName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
//firefox浏览器
//firefox浏览器User-Agent字符串:
//Mozilla/5.0 (Windows NT 6.1; WOW64; rv:36.0) Gecko/20100101 Firefox/36.0
}
request.setCharacterEncoding("UTF-8");//设置request的编码方式，防止中文乱码
if (suffix != null && suffix.equals(".xlsx")) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else {
response.setContentType("application/vnd.ms-excel");
}
response.setHeader("Content-disposition", "attachment;filename=" + recommendedName + (suffix == null ? ".xls" : suffix));
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}如何取一个单元格里的手机号?
import java.io.IOException;
import java.io.InputStream;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.WorkbookFactory;
/**
* 从excel中读取第一个Sheet的第一列,没有表头。返回一组不重复的手机号
* @author zhengzhong
*
*/
public class ReadColumnPhoneExcel {
private static Log log = LogFactory.getLog(ReadColumnPhoneExcel.class);
public static Set readPhones(InputStream is) {
Set phones = new HashSet();
// 检测代码
try {
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
// OPCPackage pkg = OPCPackage.open(file.getInputStream());
Workbook xwb = WorkbookFactory.create(is);
// 读取第一章表格内容
Sheet sheet = xwb.getSheetAt(0);
// 定义 row、cell
Row row;
// 循环输出表格中的内容
int total = 0;
int count = 0;
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null || row.getCell(0) == null) {
continue;
}
total++;
// 只读取第一列
try {
Long cellVal = 0l;
Cell curCell = row.getCell(0);
int type = curCell.getCellType();
if (type == Cell.CELL_TYPE_NUMERIC) {
cellVal = Double.valueOf(curCell.getNumericCellValue()).longValue();
} else if (type == Cell.CELL_TYPE_STRING) {
cellVal = Long.valueOf(curCell.getStringCellValue().trim());
}
long phone = cellVal.longValue();
if (phone != 0) {
// 总共11位,开始为"1"
if (String.valueOf(phone).length() != 11 || !String.valueOf(phone).startsWith("1")) {
continue;
}
phones.add(phone);
count++;
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
String info = "上传手机号格式正确数:" + count + ",总行数:" + total;
log.info(info);
return phones;
} catch (Exception ex) {
log.error(ex.getMessage(), ex);
return phones;
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
log.error(e.getMessage(), e);
}
}
}
}
}相关阅读
微信扫描-捐赠支持
加入QQ群-技术交流
评论:
↓ 广告开始-头部带绿为生活 ↓
↑ 广告结束-尾部支持多点击 ↑