java写excel_java写excel文件工具

之前写过一个用java操作csv与excel文件的工具《操作CSV与EXCEL的工具》，它写入几十、几百行数据都没有问题。但如果写几万行，就有些吃力了，速度太慢。另外也没有抽出工具代码，与业务代码耦合性太强，为此写了下面这个工具。

特点:

1.复用文件流，高效

2.业务代码可定义每一行的数据，与输入多行数据相比自由度更大

3.使用简单，只需调用一个方法

约束:

依赖poi类库

工具源码:

package com.xx.web.utils.Excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

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;

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) {

XSSFWorkbookwb = 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 row=jsonArray.getJSONArray(i);

String val[]=new String[row.size()];

this.writeExcel(sheet, rowNum++, Arrays.asList(row.toArray(val)));

}

}

});

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 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 (!file.exists()) {

// 构造 XSSFWorkbook 对象，strPath 传入文件路径

if (fileName.endsWith("xls")) { // Excel 2003

wb = new HSSFWorkbook();

} else if (fileName.endsWith("xlsx")) { // Excel 2007/2010

wb = new XSSFWorkbook();

}

} else {

FileInputStream fis = new FileInputStream(file);

if (fileName.endsWith("xls")) { // Excel 2003

wb = new HSSFWorkbook(fis);

} else if (fileName.endsWith("xlsx")) { // Excel 2007/2010

wb = new XSSFWorkbook(fis);

}

}

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 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 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));

}

}

}

}

}

调用举例,只写一个Sheet:WriteExcel.writeDatas(fileNameItem, new WriteSheet() {

//写一个Sheet

@Override

public void write(Sheet sheet) {

int rowNum=1;

// TODO Auto-generated method stub

for(City city:citys){

Map fq=new HashMap();

fq.put("artisan_city", city.getCityCode());

List rst=querySolrTool.queryItem("item","id,artisan_city", fq,"rank_old","desc", 0, 5000,ItemCity.class);

for(ItemCity itemCity:rst){

this.writeExcel(sheet, rowNum++, Arrays.asList(itemCity.getId(),String.valueOf(itemCity.getCityCode())));

if(rowNum%100==0){

log.info("rowNum:"+rowNum);

}

}

log.info("rowNum:"+rowNum);

}

}

});

写多个Sheet,加合并调用:

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>,可直接调用工具里的方法写:

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);

}

}

}

}

}

评论:

提交