poi通用导入导出Excel

原创于 2020-05-13 15:29:47 发布 · 260 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#poi #java #excel

本文详细介绍了一种使用Java和Apache POI库进行Excel数据导入和导出的方法。包括从前台上传Excel文件到后台解析数据并批量插入数据库，以及如何从数据库导出数据到Excel文件。提供了具体的代码实现和两种导出方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前台普通的form表单

<form action="" id="form_id" enctype="multipart/form-data" method="post">
     <table class="table table-bordered  formtable " >
         <tr>
             <td align="center" style="padding-top: 2%;">数据文件：</td>
             <td align="center" style="padding-top: 12px;">
                 <input name="uploadFile" type="file" onchange="checkFiles();"  style="width:90%;"/></td>
             <td align="center">
                 <a class="btn"  href="javascript:uploadFile();">
                     <i class="icon-download-alt"></i> 下载模板
                 </a>
                 <input type="button" class="btn btn-success" value="导&nbsp;入" onclick="checkSubmit();" />

                 <input type="hidden" name="importId" value="<%=importId%>"/>
             </td>
         </tr>
     </table>
 </form>

后台代码

Connection con = null;
try {
     con = DbConnectionManager.getConnection();
     POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(uploadFile));
     HSSFWorkbook wb = new HSSFWorkbook(fs);
     HSSFSheet hssfSheet = wb.getSheetAt(0);  // 获取第一个Sheet页
     if (hssfSheet != null) {
         String importDataSql = "SELECT * FROM jpas_import_data_detail WHERE id=?";
         Map<String, Object> importDataMap = JdbcUtils.queryToMap(importDataSql, importId);
         String sql = MapUtils.getString(importDataMap, "import_sql");
         int num = MapUtils.getIntValue(importDataMap, "import_num");

         PreparedStatement ps = con.prepareStatement(sql);
         for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
             HSSFRow hssfRow = hssfSheet.getRow(rowNum);
             if (hssfRow == null) {
                 continue;
             }
             for (int j = 0; j < num; j++) {
                 ps.setString(j+1, ExcelUtil.formatCell(hssfRow.getCell(j)));
             }
             ps.addBatch();
         }
         ps.executeBatch();
         if (ps != null) {
             ps.close();
         }
     }
 } catch (SQLException e) {
     log.error("导入数据出错");
 } catch (Exception e){
     e.printStackTrace();
 } finally {
     //关闭连接
     DbConnectionManager.closeConnection(con);
 }

工具包ExcelUtil

import java.io.InputStream;
import java.sql.ResultSet;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

	public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
		int rowIndex=0;
		Sheet sheet=wb.createSheet();
		Row row=sheet.createRow(rowIndex++);
		for(int i=0;i<headers.length;i++){
			row.createCell(i).setCellValue(headers[i]);
		}
		while(rs.next()){
			row=sheet.createRow(rowIndex++);
			for(int i=0;i<headers.length;i++){
				row.createCell(i).setCellValue(rs.getObject(i+1).toString());
			}
		}
	}
	
	public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
		InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java1234/template/"+templateFileName);
		POIFSFileSystem fs=new POIFSFileSystem(inp);
		Workbook wb=new HSSFWorkbook(fs);
		Sheet sheet=wb.getSheetAt(0);
		// 获取列数
		int cellNums=sheet.getRow(0).getLastCellNum();
		int rowIndex=1;
		while(rs.next()){
			Row row=sheet.createRow(rowIndex++);
			for(int i=0;i<cellNums;i++){
				row.createCell(i).setCellValue(rs.getObject(i+1).toString());
			}
		}
		return wb;
	}
	
	public static String formatCell(HSSFCell hssfCell){
		if(hssfCell==null){
			return "";
		}else{
			if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
				return String.valueOf(hssfCell.getBooleanCellValue());
			}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				return String.valueOf(hssfCell.getNumericCellValue());
			}else{
				return String.valueOf(hssfCell.getStringCellValue());
			}
		}
	}
}

导出，同样利用ExcelUtil

方案1：直接导出

Workbook wb=new HSSFWorkbook();
String headers[]={"编号","姓名","电话","Email","QQ"};
ResultSet rs=userDao.userList(con, null);//查到需导出的结果集
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(response, wb, "导出excel.xls");

方案2：利用模板导出

ResultSet rs=userDao.userList(con, null);
ResultSet rs=userDao.userList(con, null);//查到需导出的结果集
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(rs, "template.xls");

response.setHeader("Content-Disposition", "attachment;filename="+new String("利用模版导出excel.xls".getBytes("utf-8"),"iso8859-1"));
		response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();