java 中Excel的导入导出
部分转发原作者https://www.cnblogs.com/qdhxhz/p/8137282.html雨点的名字 的内容
java代码中的导入导出
首先在d盘创建一个xlsx文件，然后再进行一系列操作
package com.aynu.excel; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /*我们在d盘下建立的是一个空白的xlsx文件*/ public class ExcelSample1 { //创建一个Excel文件 public static void main(String[] args) throws Exception { //创建Excel文档对象 HSSFWorkbook wb = new HSSFWorkbook(); //创建Excel文件 //将workbook.xlsx文件转换成一个输出流对象 FileOutputStream fileout = new FileOutputStream("D:\\workbook.xlsx"); //将输出流对象写入到Excel文档对象中 wb.write(fileout); //为了避免占用资源浪费内存将流对象关闭 fileout.close(); } }
first and foremost let's look at a few objects
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
在poi1.7中才有以下2项：
HSSFHeader sheet头
HSSFFooter sheet尾（只有打印的时候才能看到效果）
和这个样式
HSSFCellStyle cell样式
辅助操作包括
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
package com.aynu.excel; import java.io.FileOutputStream; import java.util.Date; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class CreatCells { public static void main(String[] args) throws Exception { //创建一个Excel文档对象 HSSFWorkbook wb = new HSSFWorkbook(); //创建新的sheet对象 HSSFSheet sheet = wb.createSheet("new sheet"); //在sheet里创建一行，参数为行号（第一行，此处可想象成数组） HSSFRow row = sheet.createRow((short)0); //在row里建立新cell（单元格），参数为列号（第一列） HSSFCell cell = row.createCell((short)0); //设置单元格类型的值 cell.setCellValue(1);//设置Cell整数类型的值 //Cell还可以设置各种类型的值但是要先创建这个单元格 row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值 row.createCell((short)2).setCellValue(true);//设置cell布尔类型的值 row.createCell((short)3).setCellValue("test"); //设置cell字符类型的值 //如果要建立单元格的样式就要创建样式的对象 HSSFCellStyle style = wb.createCellStyle(); //设置指定的日期格式 style.setDataFormat(HSSFDataFormat. getBuiltinFormat("m/d/yy h:mm")); HSSFCell dCell =row.createCell((short)4); dCell.setCellValue(new Date()); //设置cell为日期类型的值 dCell.setCellStyle(style); //设置该cell日期的显示格式 HSSFCell csCell =row.createCell((short)5); csCell.setCellValue("中文测试_Chinese Words Test"); //设置中西文结合字符串 row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR); //建立错误cell FileOutputStream fileOut = new FileOutputStream("workbook.xlsx"); wb.write(fileOut); fileOut.close(); } }
这个代码差生的效果是
在上边的例子里我们看到了要设置一个单元格里面信息的格式（例如，要将信息居中）设置的操作如下：
|
HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cell.setCellStyle(cellstyle); |
还有我们我们经常会用到的合并单元格，在这里我们也有这样的操作，代码如下：
|
sheet.addMergedRegion(new Region(1,(short)1,2,(short)4)); |
springmvc中的Excel的导入导出
springMVC生成excel文件并导出
从上面的写法中我们就可以明白需要创建的对象
1、生成文档对象HSSHWorkbook。 2、通过HSSFWorkbook生成表单HSSFSheet。 3、通过HSSFSheet生成行HSSFRow 4、通过HSSFRow生成单元格HSSFCell。
步骤如下：
1.导入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
2.创建model对象
public class Person { private String id; private String name; private String password; private String age; public Person(String id, String name, String password, String age) { super(); this.id = id; this.name = name; this.password = password; this.age = age; } //提供set和get方法 }
3.创建页面.jsp文件
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <html> <!-- 正常数据导出肯定要传入参数，我这里没有用ajax传参，简单用链接传参 --> <script type="text/javascript"> function download(){ var url="download_excel?id=10&name=张三"; window.open(url); } </script> <body> <form action=""> <input type="button" value="报表导出" onclick="download()"/> </form> </body> </html>
4.创建控制器Controller
import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import com.ssm.service.impl.ExcleImpl; @Controller public class ExcleController { //这里直接new了 ExcleImpl excleImpl=new ExcleImpl(); @RequestMapping(value="/jsp/download_excel") //获取url链接上的参数 public @ResponseBody String dowm(HttpServletResponse response,@RequestParam("id") String id,@RequestParam("name") String name){ response.setContentType("application/binary;charset=UTF-8"); try{ ServletOutputStream out=response.getOutputStream(); try { //设置文件头：最后一个参数是设置下载文件名(这里我们叫：张三.pdf) response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(name+".xls", "UTF-8")); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } String[] titles = { "用户id", "用户姓名", "用户密码", "用户年龄" }; excleImpl.export(titles, out); return "success"; } catch(Exception e){ e.printStackTrace(); return "导出信息失败"; } } }
第五步、ExcleImpl 报表导出实现层
import java.util.ArrayList; import javax.servlet.ServletOutputStream; 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 com.ssm.model.Person; public class ExcleImpl { public void export(String[] titles, ServletOutputStream out) throws Exception{ try{ // 第一步，创建一个workbook，对应一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet hssfSheet = workbook.createSheet("sheet1"); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = hssfSheet.createRow(0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle hssfCellStyle = workbook.createCellStyle(); //居中样式 hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell hssfCell = null; for (int i = 0; i < titles.length; i++) { hssfCell = row.createCell(i);//列索引从0开始 hssfCell.setCellValue(titles[i]);//列名1 hssfCell.setCellStyle(hssfCellStyle);//列居中显示 } // 第五步，写入实体数据 Person person1=new Person("1","张三","123","26"); Person person2=new Person("2","李四","123","18"); Person person3=new Person("3","王五","123","77"); Person person4=new Person("4","徐小筱","123","1"); //这里我把list当做数据库啦 ArrayList<Person> list=new ArrayList<Person>(); list.add(person1); list.add(person2); list.add(person3); list.add(person4); for (int i = 0; i < list.size(); i++) { row = hssfSheet.createRow(i+1); Person person = list.get(i); // 第六步，创建单元格，并设置值 String id = null; if(person.getId() != null){ id = person.getId(); } row.createCell(0).setCellValue(id); String name = ""; if(person.getName() != null){ name = person.getName(); } row.createCell(1).setCellValue(name); String password = ""; if(person.getPassword() != null){ password = person.getPassword(); } row.createCell(2).setCellValue(password); String age=null; if(person.getAge() !=null){ age = person.getAge(); } row.createCell(3).setCellValue(age); } // 第七步，将文件输出到客户端浏览器 try { workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }catch(Exception e){ e.printStackTrace(); throw new Exception("导出信息失败！"); } } }
第六步：最终效果，当我点击报表导出按钮
springMVC导入excel文件数据到数据库
第一步、导入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
第二步，创建Model对象
public class Family { //家庭编号 private String jtbh; //姓名 private String xm; //行业 private String hy; //备注 private String bz; /* * 提供set和get，toString方法 */ }
第三步.导入excel界面 leadingexcel.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <html> <head> <script type="text/javascript" src="../js/jquery-1.7.1.js"></script> <script type="text/javascript" src="../js/jquery.form.js"></script> <script type="text/javascript"> /* ajax 方式上传文件操作 */ $(document).ready(function(){ $("#btn").click(function(){ if(checkData()){ $('#form1').ajaxSubmit({ url:'uploadExcel/ajax', dataType: 'text', success: resutlMsg, error: errorMsg }); function resutlMsg(msg){ alert(msg); $("#upfile").val(""); } function errorMsg(){ alert("导入excel出错！"); } } }); }); //JS校验form表单信息 function checkData(){ var fileDir = $("#upfile").val(); var suffix = fileDir.substr(fileDir.lastIndexOf(".")); if("" == fileDir){ alert("选择需要导入的Excel文件！"); return false; } if(".xls" != suffix && ".xlsx" != suffix ){ alert("选择Excel格式的文件导入！"); return false; } return true; } </script> </head> <body> <form method="POST" enctype="multipart/form-data" id="form1" action="uploadExcel/form"> <label>上传文件: </label> <input id="upfile" type="file" name="upfile"><br> <br> <input type="submit" value="表单提交" onclick="return checkData()"> <input type="button" value="ajax提交" id="btn" name="btn" > </form> </body> </html>
先讲下，我这src引用路径的时候发现，怎么也引用不到，找了好久才发现，我在springmvc中没有配置静态文件
springmvc.xml
<!-- 静态资源访问 -->
<mvc:default-servlet-handler/>
<!-- 当我仅配置上面的时候又发现src是引用到了，但是我的RequestMapping映射却变成请求不到了，所以下面的也一定要加上 -->
<mvc:annotation-driven></mvc:annotation-driven>
第四步、LeadingExcelController.java
import java.io.InputStream; import java.io.PrintWriter; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import com.ssm.model.Family; import com.ssm.service.impl.ImportExcelUtil; @Controller @RequestMapping("/jsp/uploadExcel") public class LeadingExcelController { @RequestMapping("/form") public String form(HttpServletRequest request)throws Exception{ MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; InputStream in =null; List<List<Object>> listob = null; MultipartFile file = multipartRequest.getFile("upfile"); if(file.isEmpty()){ throw new Exception("文件不存在！"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); in.close(); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); Family family = new Family(); family.setJtbh(String.valueOf(lo.get(0))); family.setXm(String.valueOf(lo.get(1))); family.setHy(String.valueOf(lo.get(2))); family.setBz(String.valueOf(lo.get(3))); System.out.println("打印信息-->"+family.toString()); } return null; } @RequestMapping(value="/ajax") public void ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; InputStream in =null; List<List<Object>> listob = null; MultipartFile file = multipartRequest.getFile("upfile"); if(file.isEmpty()){ throw new Exception("文件不存在！"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); Family family = new Family(); family.setJtbh(String.valueOf(lo.get(0))); family.setXm(String.valueOf(lo.get(1))); family.setHy(String.valueOf(lo.get(2))); family.setBz(String.valueOf(lo.get(3))); System.out.println("打印信息-->"+family.toString()); } PrintWriter out = null; response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码 out = response.getWriter(); out.print("文件导入成功！"); out.flush(); out.close(); } }
第五步、ImportExcelUtil.java 报表导入实现层
import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; 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.xssf.usermodel.XSSFWorkbook; public class ImportExcelUtil { private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel /** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException */ public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{ List<List<Object>> list = null; //创建Excel工作薄 Workbook work = this.getWorkbook(in,fileName); if(null == work){ throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if(sheet==null){continue;} //遍历当前sheet中的所有行 for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if(row==null||row.getFirstCellNum()==j){continue;} //遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); li.add(this.getCellValue(cell)); } list.add(li); } } in.close(); return list; } /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */ public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{ Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if(excel2003L.equals(fileType)){ wb = new HSSFWorkbook(inStr); //2003- }else if(excel2007U.equals(fileType)){ wb = new XSSFWorkbook(inStr); //2007+ }else{ throw new Exception("解析的文件格式有误！"); } return wb; } /** * 描述：对表格中数值进行格式化 * @param cell * @return */ public Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if("General".equals(cell.getCellStyle().getDataFormatString())){ value = df.format(cell.getNumericCellValue()); }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){ value = sdf.format(cell.getDateCellValue()); }else{ value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } }
第六步：最终效果，当我点击通过表单和ajax提交时
(1)先展示上传的xls文件内容
(2)最终控制台打出：
(3)通过ajax导入成功，前端也会提示
