POI操作Excel完整示例
package com.servlet;import java.io.File;import java.io.IOException;import java.io.PrintWriter;import java.util.Iterator;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.fileupload.FileItem;import org.apache.commons.fileupload.disk.DiskFileItemFactory;import org.apache.commons.fileupload.servlet.ServletFileUpload;import com.service.ExcelService;/** * 导入 */public class ImportServlet extends HttpServlet {private static final long serialVersionUID = 1L; private ExcelService excelService; public ImportServlet() { excelService = new ExcelService(); }protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doPost(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {response.setContentType("text/html;charset=utf-8");PrintWriter out = response.getWriter();DiskFileItemFactory factory = new DiskFileItemFactory() ;factory.setSizeThreshold( 8192 ) ;String tempPath = request.getSession().getServletContext().getRealPath("/")+ "/upload" ; File tempDir = new File(tempPath) ;if(!tempDir.isDirectory()){tempDir.mkdirs();}factory.setRepository(tempDir) ; ServletFileUpload upload = new ServletFileUpload(factory) ; List<FileItem> items;try {items = upload.parseRequest(request);Iterator<FileItem> itr = items.iterator(); while (itr.hasNext()) {// 依次处理每个 form field FileItem item = (FileItem) itr.next(); if(!item.isFormField()){ /* 判断是否为表单控件（非File控件），如果不是表单控件，则上传此文件 */// File savedFile = new File(tempPath,item.getName() ) ; // 由于不同的浏览器可能取得的文件的名字不同，有的浏览器将整个路径取道，有的浏览器只取到文件名 int start = item.getFieldName().lastIndexOf("\"); String fileName = item.getFieldName().substring(start + 1); File savedFile = new File(tempPath,fileName) ; item.write(savedFile) ; //解析 String msg = excelService.importExcel(savedFile); System.out.println(msg); out.println(msg); }else{/* 如果是表单控件，则保存其值*/ System.out.println( item.getFieldName() + "-->" + item.getString() ) ; }}}catch (Exception e){e.printStackTrace() ; }finally{if(out != null){out.close();}}}}
?
导入解析
/** * 解析Excel * @param excelFile * @throws Exception */public String importExcel(File excelFile) throws Exception{if(excelFile == null){throw new Exception("参数[excelFile]为空.");}FileInputStream is = new FileInputStream(excelFile); HSSFWorkbook wb=new HSSFWorkbook(is); StringBuilder sb = new StringBuilder(); int sheetNum=wb.getNumberOfSheets(); System.out.println("Excel页数：" + sheetNum);; for(int i=0;i<sheetNum;i++) { HSSFSheet childSheet = wb.getSheetAt(i); int rowNum = childSheet.getLastRowNum(); //行数-1 System.out.println("Excel记录行数：" + rowNum); //j=1 以第二行开始遍历 第一行为标题栏 for(int j=1;j<=rowNum;j++){ HSSFRow row = childSheet.getRow(j); sb.append("\n编号:").append(row.getCell(0).toString()) .append("\n姓名:").append(row.getCell(1).toString()) .append("\n性别:").append(row.getCell(2).toString()) .append("\n出生日期:").append(row.getCell(3).toString()); } } return sb.toString();}?
?
导出
?
ExportServlet
?
package com.servlet;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.service.ExcelService;import com.utils.ServletUtils;/** * 导出 */public class ExportServlet extends HttpServlet {private static final long serialVersionUID = 1L; private ExcelService excelService; public ExportServlet() { excelService = new ExcelService(); }protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doPost(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {OutputStream outputStream = response.getOutputStream();InputStream inputStream = excelService.exportExcel();//设置文件类型response.setContentType(ServletUtils.EXCEL_TYPE);//弹出下载对话框ServletUtils.setFileDownloadHeader(request, response, "Student.xls");byte[] buffer = new byte[1024]; int bytesRead; while ((bytesRead = inputStream.read(buffer)) != -1){ outputStream.write(buffer, 0, bytesRead); } outputStream.close();}}?
导出构造数据
/** * 导出Excel * @return */public InputStream exportExcel(){HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("sheet1");HSSFRow row = sheet.createRow(0);HSSFCell cell = row.createCell((short) 0);cell.setCellValue("编号");cell = row.createCell((short) 1);cell.setCellValue("姓名");cell = row.createCell((short) 2);cell.setCellValue("性别");cell = row.createCell((short) 3);cell.setCellValue("出生日期");List<Student> students = studentService.getStudents();for (int i = 0; i < students.size(); ++i){Student student = students.get(i);row = sheet.createRow(i + 1);cell = row.createCell((short) 0);cell.setCellValue(student.getId());cell = row.createCell((short) 1);cell.setCellValue(student.getName());cell = row.createCell((short) 2);cell.setCellValue(student.getSex());cell = row.createCell((short) 3);cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(student.getBirthDate()));} ByteArrayOutputStream os = new ByteArrayOutputStream();try{wb.write(os);}catch (IOException e){e.printStackTrace();}byte[] content = os.toByteArray();InputStream is = new ByteArrayInputStream(content);return is;}?完整代码参考附件