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

文/程忠 浏览次数:0次   2018-04-03 15:34:15

相关阅读

微信扫描-捐赠支持
加入QQ群-技术交流

评论:
点击刷新