Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程式对Microsoft Office格式档案读和写的功能。我们这里使用poi对数据库中的数据进行批量导出,以及从Excel文件中的数据批量导入到数据库中。
批量导出:
步骤:1.导入架包:
poi-3.0-rc4-20070503.jar、poi-contrib-3.0-rc4-20070503.jar、poi-scratchpad-3.0-rc4-20070503.jar
2.Excel操纵类,可以根据Excel模板来生成Excel对象(模板代码)
3.生成Excel文件提供下载
实例代码:
Excel操纵类:
- package cn.test.excel;
- import java.util.Date;
- import java.util.HashMap;
- import java.util.Iterator;
- import java.util.Map;
- import java.util.Properties;
- import java.util.Set;
- import org.apache.commons.logging.Log;
- import org.apache.commons.logging.LogFactory;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- /**
- *
- * 功能描述: Excel操纵类,可以根据Excel模板来生成Excel对象<br>
- * 版本信息:1.0 <br>
- * Copyright: Copyright (c) 2005<br>
- */
- public class ExcelTemplate {
- private static Log logger = LogFactory.getLog(ExcelTemplate.class);
- private static final String DATAS = "datas";
- private HSSFWorkbook workbook;
- private HSSFSheet sheet;
- private HSSFRow currentRow;
- private Map styles = new HashMap(); //数据行的默认样式配置
- private Map confStyles = new HashMap(); //通过设置"#STYLE_XXX"来标识的样式配置
- private int initrow; //数据输出起始行
- private int initcol; //数据输出起始列
- private int num; //index number
- private int currentcol; //当前列
- private int currentRowIndex; //当前行index
- private int rowheight = 22; //行高
- private int lastLowNum = 0;
- private String cellStyle = null;
- private ExcelTemplate() {
- }
- /**
- * 使用默认模板创建ExcelTemplate对象
- * @return 根据模板已初始化完成的ExcelTemplate对象
- */
- public static ExcelTemplate newInstance(){
- return newInstance("templates/default.xls");
- }
- /**
- * 指定模板创建ExcelTemplate对象
- * @param templates 模板名称
- * @return 根据模板已初始化完成的ExcelTemplate对象
- */
- public static ExcelTemplate newInstance(String templates){
- try {
- ExcelTemplate excel = new ExcelTemplate();
- POIFSFileSystem fs = new POIFSFileSystem(
- Thread.currentThread().getContextClassLoader()
- .getResourceAsStream(templates));
- excel.workbook = new HSSFWorkbook(fs);
- excelexcel.sheet = excel.workbook.getSheetAt(0);
- //查找配置
- excel.initConfig();
- //查找其它样式配置
- excel.readCellStyles();
- //删除配置行
- excel.sheet.removeRow( excel.sheet.getRow(excel.initrow) );
- return excel;
- } catch (Exception e) {
- e.printStackTrace();
- logger.trace("创建Excel对象出现异常",e);
- throw new RuntimeException("创建Excel对象出现异常");
- }
- }
- /**
- * 设置特定的单元格样式,此样式可以通过在模板文件中定义"#STYLE_XX"来得到,如:
- * #STYLE_1,传入的参数就是"STYLE_1"
- * @param style
- */
- public void setCellStyle(String style){
- cellStyle = style;
- }
- /**
- * 取消特定的单元格格式,恢复默认的配置值,即DATAS所在行的值
- */
- public void setCellDefaultStyle(){
- cellStyle = null;
- }
- /**
- * 创建新行
- * @param index 从0开始计数
- */
- public void createRow(int index){
- //如果在当前插入数据的区域有后续行,则将其后面的行往后移动
- if(lastLowNum > initrow && index > 0){
- sheet.shiftRows(index + initrow ,lastLowNum + index,1,true,true);
- }
- currentRow = sheet.createRow(index + initrow);
- currentRow.setHeight((short)rowheight);
- currentRowIndex = index;
- currentcol = initcol;
- }
- /**
- * 根据传入的字符串值,在当前行上创建新列
- * @param value 列的值(字符串)
- */
- public void createCell(String value){
- HSSFCell cell = createCell();
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setCellValue(value);
- }
- /**
- * 根据传入的日期值,在当前行上创建新列
- * 在这种情况下(传入日期),你可以在模板中定义对应列
- * 的日期格式,这样可以灵活通过模板来控制输出的日期格式
- * @param value 日期
- */
- public void createCell(Date value){
- HSSFCell cell = createCell();
- cell.setCellValue(value);
- }
- /**
- * 创建当前行的序列号列,通常在一行的开头便会创建
- * 注意要使用这个方法,你必需在创建行之前调用initPageNumber方法
- */
- public void createSerialNumCell(){
- HSSFCell cell = createCell();
- cell.setCellValue(currentRowIndex + num);
- }
- private HSSFCell createCell(){
- HSSFCell cell = currentRow.createCell((short)currentcol++);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- HSSFCellStyle style = (HSSFCellStyle)styles.get(new Integer(cell.getCellNum()));
- if(style != null){
- cell.setCellStyle(style);
- }
- //设置了特定格式
- if(cellStyle != null){
- HSSFCellStyle ts = (HSSFCellStyle)confStyles.get(cellStyle);
- if(ts != null){
- cell.setCellStyle(ts);
- }
- }
- return cell;
- }
- /**
- * 获取当前HSSFWorkbook的实例
- * @return
- */
- public HSSFWorkbook getWorkbook(){
- return workbook;
- }
- /**
- * 获取模板中定义的单元格样式,如果没有定义,则返回空
- * @param style 模板定义的样式名称
- * @return 模板定义的单元格的样式,如果没有定义则返回空
- */
- public HSSFCellStyle getTemplateStyle(String style){
- return (HSSFCellStyle)confStyles.get(style);
- }
- /**
- * 替换模板中的文本参数
- * 参数以“#”开始
- * @param props
- */
- public void replaceParameters(Properties props){
- if(props == null || props.size() == 0){
- return;
- }
- Set propspropsets = props.entrySet();
- Iterator rowit = sheet.rowIterator();
- while(rowit.hasNext()){
- HSSFRow row = (HSSFRow)rowit.next();
- if(row == null) continue;
- int cellLength = row.getLastCellNum();
- for(int i=0; i<cellLength; i++){
- HSSFCell cell = (HSSFCell)row.getCell((short)i);
- if(cell == null) continue;
- String value = cell.getStringCellValue();
- if(value != null && value.indexOf("#") != -1){
- for (Iterator iter = propsets.iterator(); iter.hasNext();) {
- Map.Entry entry = (Map.Entry) iter.next();
- valuevalue = value.replaceAll("#"+entry.getKey(),(String)entry.getValue());
- }
- }
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(value);
- }
- }
- }
- /**
- * 初始化Excel配置
- */
- private void initConfig(){
- lastLowNum = sheet.getLastRowNum();
- Iterator rowit = sheet.rowIterator();
- boolean configFinish = false;
- while(rowit.hasNext()){
- if(configFinish){
- break;
- }
- HSSFRow row = (HSSFRow)rowit.next();
- if(row == null) continue;
- int cellLength = row.getLastCellNum();
- int rowrownum = row.getRowNum();
- for(int i=0; i<cellLength; i++){
- HSSFCell cell = (HSSFCell)row.getCell((short)i);
- if(cell == null) continue;
- String config = cell.getStringCellValue();
- if(DATAS.equalsIgnoreCase(config)){
- //本行是数据开始行和样式配置行,需要读取相应的配置信息
- initrow = row.getRowNum();
- rowrowheight = row.getHeight();
- initcol = cell.getCellNum();
- configFinish = true;
- }
- if(configFinish){
- readCellStyle(cell);
- }
- }
- }
- }
- /**
- * 读取cell的样式
- * @param cell
- */
- private void readCellStyle(HSSFCell cell){
- HSSFCellStyle style = cell.getCellStyle();
- if(style == null) return;
- styles.put(new Integer(cell.getCellNum()),style);
- }
- /**
- * 读取模板中其它单元格的样式配置
- */
- private void readCellStyles(){
- Iterator rowit = sheet.rowIterator();
- while(rowit.hasNext()){
- HSSFRow row = (HSSFRow)rowit.next();
- if(row == null) continue;
- int cellLength = row.getLastCellNum();
- for(int i=0; i<cellLength; i++){
- HSSFCell cell = (HSSFCell)row.getCell((short)i);
- if(cell == null) continue;
- String value = cell.getStringCellValue();
- if(value != null && value.indexOf("#STYLE_") != -1){
- HSSFCellStyle style = cell.getCellStyle();
- if(style == null) continue;
- confStyles.put(value.substring(1),style);
- //remove it
- row.removeCell(cell);
- }
- }
- }
- }
- }
生成Excel文件并提供下载
- package cn.test.web.manager;
- import java.io.IOException;
- import java.util.List;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import cn.itcast.dao.impl.BookDaoImpl;
- import cn.itcast.domain.Book;
- import cn.itcast.excel.ExcelTemplate;
- public class ExcelServlet extends HttpServlet {
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- //导出Excel文件,不需要返回值,因为在方法的内部已经处理完成response
- //HttpServletRequest request =ServletActionContext.getRequest();
- String chcCreateDate=request.getParameter("svrDueId");
- BookDaoImpl bd =new BookDaoImpl();
- List customers = bd.getAll();//从数据库中获取要导出的集合
- //获取模板样式,需自行创建
- ExcelTemplate template = ExcelTemplate.newInstance("cn/test/excel/export_template.xls");
- for(int i=0; i<customers.size(); i++){
- Book book = (Book)customers.get(i);
- //创建一行
- template.createRow(i);
- //创建列
- template.createCell(book.getId().toString());
- template.createCell(book.getName().toString());
- template.createCell(book.getAuthor());
- template.createCell(book.getDescription());
- }
- //提供下载
- //HttpServletResponse response = ServletActionContext.getResponse();
- response.reset();
- response.setContentType("application/x-download;charset=GBK");
- response.setHeader("Content-Disposition", "attachment;filename=Book_"+System.currentTimeMillis()+".xls");
- try {
- template.getWorkbook().write(response.getOutputStream());
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- }
- }
批量导入:
步骤:1.导入架包:
poi-3.0-rc4-20070503.jar、poi-contrib-3.0-rc4-20070503.jar、poi-scratchpad-3.0-rc4-20070503.jar
2.生成前台页面
2.生出Excel工具类,将从Excel中获取的数据进行数据类型转换(模板代码)
3.读取Excel文件批量上传
生出Excel工具类:
- package com.ssh.crm.excel;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- public class ExcelUtils {
- public static int getIntCellValue(HSSFRow row,int index){
- int rtn = 0;
- try {
- HSSFCell cell = row.getCell((short)index);
- rtn = (int)cell.getNumericCellValue();
- } catch (RuntimeException e) {
- }
- return rtn;
- }
- public static String getStringValue(HSSFRow row,int index){
- String rtn = "";
- try {
- HSSFCell cell = row.getCell((short)index);
- rtn = cell.getRichStringCellValue().getString();
- } catch (RuntimeException e) {
- }
- return rtn;
- }
- }
前台使用Struts标签库实现的
- <s:form action="UnderlyingData!importExcel.action" method="post" enctype="multipart/form-data">
- <s:file label="请选择文件" name="excel" required="true"/>
- <s:submit value="批量导入客户数据"></s:submit>
- </s:form>
读取Excel文件批量上传
- //导入Excel文件
- protected File excel;
- //封装要上传的文件
- public File getExcel() {
- return excel;
- }
- public void setExcel(File excel) {
- this.excel = excel;
- }
- public String importExcel(){
- List success = new ArrayList();
- ActionContext tx =ActionContext.getContext();
- String successanderror="";
- if(excel != null){
- try {
- //读取excel文件分析Excel文件中的数据
- HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excel));
- //读取第一页的内容
- HSSFSheet sheet = wb.getSheetAt(0);
- //从数据行开始读取数据
- System.out.println(sheet.getLastRowNum());
- for(int i=2; i<=sheet.getLastRowNum(); i++){
- HSSFRow row = sheet.getRow(i);
- Product product =new Product();
- //名称
- product.setProdName(ExcelUtils.getStringValue(row, 1));
- product.setProdType(ExcelUtils.getStringValue(row, 2));
- product.setProdBatch(ExcelUtils.getStringValue(row, 3));
- product.setProdUnit(ExcelUtils.getStringValue(row, 4));
- product.setProdPrice(new Double(ExcelUtils.getIntCellValue(row, 5)));
- //System.out.println(product.getProdPrice());
- product.setProdMemo(ExcelUtils.getStringValue(row, 6));
- //检查用户输入是否合法
- if(product.getProdName().equals("")){
- throw new Exception("名称项格式不正确,请检查第"+(i+1)+"行第"+2+"列!");
- }else if(product.getProdType().equals("")){
- throw new Exception("型号项格式不正确,请检查第"+(i+1)+"行第"+3+"列!");
- }else if(product.getProdBatch().equals("")){
- throw new Exception("等级/批次项格式不正确,请检查第"+(i+1)+"行第"+4+"列!");
- }else if(product.getProdUnit().equals("")){
- throw new Exception("单位项格式不正确,请检查第"+(i+1)+"行第"+5+"列!");
- }else if(product.getProdPrice()==0.0){
- throw new Exception("单价项格式不正确,请检查第"+(i+1)+"行第"+6+"列!");
- }else if(product.getProdMemo().equals("")){
- throw new Exception("备注项格式不正确,请检查第"+(i+1)+"行第"+7+"列!");
- }
- success.add(product);
- }
- successanderror=underlyingDataService.addproduct(success);
- tx.put("successList",success);
- } catch (Exception e) {
- successanderror=e.getMessage();
- }finally{
- tx.put("sande",successanderror);
- }
- }
- return "inputdaoru";
- }