用poi框架进行批量导入导出实例

来源：互联网发布：知乎精华推荐编辑：程序博客网时间：2024/04/29 06:38

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操纵类：

[html] view plaincopy
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);  
            excel.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 propsets = 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();  
                        value = 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 rownum = 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();  
                    rowheight = 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文件并提供下载

[html] view plaincopy
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工具类：

[html] view plaincopy
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标签库实现的

[html] view plaincopy
<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文件批量上传

[html] view plaincopy
//导入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";  
}  