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) {
		
		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 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 (!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<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));
				}
			}
		}
	}
}


调用举例,只写一个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<List<String>>,可直接调用工具里的方法写:

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群-技术交流

评论: