Java POI 导入Excel以及解析Excel
来源:互联网 发布:3g网络和4g网络的速度 编辑:程序博客网 时间:2024/05/16 14:44
用的SSM框架,所需要的jar包如图所示:,链接地址:jar包下载 ,下面直接上代码。
1、ExcelUtil工具类
- <span style="color:#993399;"><span style="font-size:18px;color:#993399;">import java.text.DecimalFormat;
- import java.text.SimpleDateFormat;
- import java.util.Calendar;
- import java.util.Date;
- import java.util.regex.Matcher;
- import java.util.regex.Pattern;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFDateUtil;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.DateUtil;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- /**
- * Excel工具类
- * @author lp
- *
- */
- public class ExcelUtil {
- public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
- public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
- public static final String EMPTY = "";
- public static final String POINT = ".";
- public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
- /**
- * 获得path的后缀名
- * @param path
- * @return
- */
- public static String getPostfix(String path){
- if(path==null || EMPTY.equals(path.trim())){
- return EMPTY;
- }
- if(path.contains(POINT)){
- return path.substring(path.lastIndexOf(POINT)+1,path.length());
- }
- return EMPTY;
- }
- /**
- * 单元格格式
- * @param hssfCell
- * @return
- */
- @SuppressWarnings({ "static-access", "deprecation" })
- public static String getHValue(HSSFCell hssfCell){
- if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
- return String.valueOf(hssfCell.getBooleanCellValue());
- } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
- String cellValue = "";
- if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
- Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
- cellValue = sdf.format(date);
- }else{
- DecimalFormat df = new DecimalFormat("#.##");
- cellValue = df.format(hssfCell.getNumericCellValue());
- String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
- if(strArr.equals("00")){
- cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
- }
- }
- return cellValue;
- } else {
- return String.valueOf(hssfCell.getStringCellValue());
- }
- }
- /**
- * 单元格格式
- * @param xssfCell
- * @return
- */
- public static String getXValue(XSSFCell xssfCell){
- if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
- return String.valueOf(xssfCell.getBooleanCellValue());
- } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
- String cellValue = "";
- if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
- Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
- cellValue = sdf.format(date);
- }else{
- DecimalFormat df = new DecimalFormat("#.##");
- cellValue = df.format(xssfCell.getNumericCellValue());
- String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
- if(strArr.equals("00")){
- cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
- }
- }
- return cellValue;
- } else {
- return String.valueOf(xssfCell.getStringCellValue());
- }
- }
- /**
- * 自定义xssf日期工具类
- * @author lp
- *
- */
- class XSSFDateUtil extends DateUtil{
- protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
- return DateUtil.absoluteDay(cal, use1904windowing);
- }
- }</span></span>
- package com.ssm.util;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.ArrayList;
- import java.util.List;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- 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.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.springframework.web.multipart.MultipartFile;
- /**
- * 读取Excel
- * @author lp
- *
- */
- public class ExcelRead {
- public int totalRows; //sheet中总行数
- public static int totalCells; //每一行总单元格数
- /**
- * read the Excel .xlsx,.xls
- * @param file jsp中的上传文件
- * @return
- * @throws IOException
- */
- public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {
- if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
- return null;
- }else{
- String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
- if(!ExcelUtil.EMPTY.equals(postfix)){
- if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
- return readXls(file);
- }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
- return readXlsx(file);
- }else{
- return null;
- }
- }
- }
- return null;
- }
- /**
- * read the Excel 2010 .xlsx
- * @param file
- * @param beanclazz
- * @param titleExist
- * @return
- * @throws IOException
- */
- @SuppressWarnings("deprecation")
- public List<ArrayList<String>> readXlsx(MultipartFile file){
- List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
- // IO流读取文件
- InputStream input = null;
- XSSFWorkbook wb = null;
- ArrayList<String> rowList = null;
- try {
- input = file.getInputStream();
- // 创建文档
- wb = new XSSFWorkbook(input);
- //读取sheet(页)
- for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
- XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
- if(xssfSheet == null){
- continue;
- }
- totalRows = xssfSheet.getLastRowNum();
- //读取Row,从第二行开始
- for(int rowNum = 1;rowNum <= totalRows;rowNum++){
- XSSFRow xssfRow = xssfSheet.getRow(rowNum);
- if(xssfRow!=null){
- rowList = new ArrayList<String>();
- totalCells = xssfRow.getLastCellNum();
- //读取列,从第一列开始
- for(int c=0;c<=totalCells+1;c++){
- XSSFCell cell = xssfRow.getCell(c);
- if(cell==null){
- rowList.add(ExcelUtil.EMPTY);
- continue;
- }
- rowList.add(ExcelUtil.getXValue(cell).trim());
- }
- }
- }
- }
- return list;
- } catch (IOException e) {
- e.printStackTrace();
- } finally{
- try {
- input.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- return null;
- }
- /**
- * read the Excel 2003-2007 .xls
- * @param file
- * @param beanclazz
- * @param titleExist
- * @return
- * @throws IOException
- */
- public List<ArrayList<String>> readXls(MultipartFile file){
- List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
- // IO流读取文件
- InputStream input = null;
- HSSFWorkbook wb = null;
- ArrayList<String> rowList = null;
- try {
- input = file.getInputStream();
- // 创建文档
- wb = new HSSFWorkbook(input);
- //读取sheet(页)
- for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
- HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
- if(hssfSheet == null){
- continue;
- }
- totalRows = hssfSheet.getLastRowNum();
- //读取Row,从第二行开始
- for(int rowNum = 1;rowNum <= totalRows;rowNum++){
- HSSFRow hssfRow = hssfSheet.getRow(rowNum);
- if(hssfRow!=null){
- rowList = new ArrayList<String>();
- totalCells = hssfRow.getLastCellNum();
- //读取列,从第一列开始
- for(short c=0;c<=totalCells+1;c++){
- HSSFCell cell = hssfRow.getCell(c);
- if(cell==null){
- rowList.add(ExcelUtil.EMPTY);
- continue;
- }
- rowList.add(ExcelUtil.getHValue(cell).trim());
- }
- list.add(rowList);
- }
- }
- }
- return list;
- } catch (IOException e) {
- e.printStackTrace();
- } finally{
- try {
- input.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- return null;
- }
- }
- <span style="color:#993399;"><pre name="code" class="java"><span style="color:#993399;">/**
- * 转到Excel上传页面
- * @return
- * @author lp
- */
- @RequestMapping(value="/read")
- public String addExcel(){
- return "baseInfo/testExcel";
- }
- /**
- * 读取Excel数据到数据库
- * @param file
- * @param request
- * @return
- * @throws IOException
- * @author lp
- */
- @RequestMapping(value="/readExcel")
- public ModelAndView readExcel(@RequestParam(value="excelFile") MultipartFile file,HttpServletRequest request,HttpSession session) throws IOException{
- ModelAndView mv = new ModelAndView();
- //判断文件是否为空
- if(file == null){
- mv.addObject("msg", "failed");
- mv.setViewName("excel_result");
- return mv;
- }
- String name = file.getOriginalFilename();
- long size = file.getSize();
- if(name == null || ExcelUtil.EMPTY.equals(name) && size==0){
- mv.addObject("msg", "failed");
- mv.setViewName("excel_result");
- return mv;
- }
- //读取Excel数据到List中
- List<ArrayList<String>> list = new ExcelRead().readExcel(file);
- //list中存的就是excel中的数据,可以根据excel中每一列的值转换成你所需要的值(从0开始),如:
- User user = null;
- List<User> liseUser = new ArrayList<User>();
- for(ArrayList<String> arr:list){
- user= new User();
- user.setAuthor(list.get(0));//每一行的第一个单元格
- listUser.add(user);
- }
- if(userService.saveBatchInsert(listUser)){
- mv.addObject("msg", "success");
- }else{
- mv.addObject("msg", "failed");
- }
- mv.setViewName("excel_result");
- return mv;
- }</span></span>
(1)主页面添加“Excel导入”
<a href="JavaScript:addExcel();"><em>Excel导入</em></a>function addExcel(){ var dg = new $.dialog({title:'导入Excel',id:'excel',width:1000,height:400,iconTitle:false,cover:true,maxBtn:false,xButton:true,resize:false,page:'user/read.html',});dg.ShowDialog();}
(2)导入页面
- <body>
- <form action="readExcel.html" enctype="multipart/form-data" method="post" id="batchAdd" name="batchAdd" target="result" onsubmit="return check();>
- <div style="margin: 30px;">
- <div><input id="excel_file" type="file" name="excelFile" size="50"/>
- </form>
- <iframe name="result" id="result" src="about:blank" frameborder="0" width="0" height="0"></iframe>
- </body>
- </html>
- <script type="text/javascript">
- var dg;
- $(document).ready(function(){
- dg = frameElement.lhgDG;
- dg.addBtn('ok','保存',function(){
- $("#batchAdd").submit();
- this.disabled=true;
- });
- });
- function success(){
- if(dg.curWin.document.forms[0]){
- dg.curWin.document.forms[0].action = dg.curWin.location+"";
- dg.curWin.document.forms[0].submit();
- }else{
- dg.curWin.location.reload();
- }
- dg.cancel();
- }
- function failed(){
- alert("上传失败!");
- }
- </script>
- <body>
- <script type="text/javascript">
- var msg = "${msg}";
- if(msg=="success" || msg==""){
- alert("保存成功");
- parent.success();
- }else{
- parent.failed();
- }
- </script>
- </body>
service
boolean saveBatchInsert(List<User> listUser);
serviceimpl
@Transactional
public boolean saveBatchInsert(List<User> listUser){
boolean flag = false;
if(listBaseInfo != null){
userMapper.batchInsert(listUser);
flag = true;
}
return flag;
}
6、dao层
void batchInsert(List<User> list);
- <insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false" keyProperty="Id">
- insert into baseinfo(ID,Name,Sex,Birth,IdCardNo,Address,Tel,Author,AddDate,Status)
- <foreach collection="list" item="item" index="index" separator="union all">
- (select
- <span style="white-space:pre"> </span>#{item.id,jdbcType=NUMERIC},
- #{item.name,jdbcType=VARCHAR},
- #{item.sex,jdbcType=CHAR},
- #{item.birth,jdbcType=DATE},
- #{item.idCardNo,jdbcType=CHAR},
- #{item.address,jdbcType=VARCHAR},
- #{item.tel,jdbcType=VARCHAR},
- #{item.author,jdbcType=VARCHAR},
- #{item.addDate,jdbcType=DATE},
- #{item.status,jdbcType=CHAR}
- from dual)
- </foreach>
- </insert>
Java POI 导入Excel并解析
导入Excel表的数据,主要包括web端上传部分和spring后端代码。
1、Web页面代码
<input type="file" name="uploadfile" id="uploadfile" >,注意form里需增加encType="multipart/form-data"
2、spring后台接收
参数接收中增加@RequestParam("excelFile")MultipartFile excelFile
3、Excel文件解析
核心代码如下:
- try{
- InputStream inputStream = excelFile.getInputStream();
- POIFSFileSystem fs = new POIFSFileSystem(inputStream);
- HSSFWorkbook workbook = new HSSFWorkbook(fs);
- //XSSFWorkbook workbook = new XSSFWorkbook(fs);
- HSSFSheet sheet = workbook.getSheetAt(0);
- int rowNum = sheet.getLastRowNum();// 行
- int cellNum;
- HSSFRow row;
- HSSFCell cell;
- String value = "";
- for (int i = 1; i <= rowNum; i++) {
- row = sheet.getRow(i);
- cellNum = row.getLastCellNum();// 列
- recStrb = new StringBuffer("");
- for (int j = 0; j < cellNum; j++) {//对一行的每个列进行解析
- cell = row.getCell((short) j);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- if(j == 0){
- value = String.valueOf((int)cell.getNumericCellValue());//数字型的值
- }else if(j == 1){
- value = String.valueOf((int)cell.getNumericCellValue());
- }else if(j == 2){//
- value = cell.getStringCellValue();//字符型的值
- }else{
- }
- if (StringUtils.isEmpty(value)) {
- recStrb.append("null, ");
- } else {
- recStrb.append("'" + value + "', ");//对取得的值进行处理
- }
- }
- //数据的自定义处置
- if (i > 0) {
- // log.debug(recStrb.toString());
- recStrb.append("'"+super_parts_code+"', ");
- String strTemp = recStrb.toString();
- strTemp = strTemp.substring(0,strTemp.lastIndexOf(","));
- dataList.add(strTemp);
- }
- }
- Java POI 导入Excel以及解析Excel
- Java POI 导入Excel并解析
- java poi 导入excel
- Java poi+excel导入
- java poi导入EXCEL
- java poi 导入Excel
- Java POI导入Excel
- Java POI 导入Excel
- java poi 导入excel
- Java poi+excel导入
- java poi 解析excel
- java POI解析Excel
- java excel poi 导入导出
- JAVA导入导出EXCEL(POI)
- Java POI导入导出excel
- Java POI Excel的导入
- java poi实现excel导入
- java poi Excel文件导入
- JAVA源码解析(4)-java.beans.EventSetDescriptor
- 哈夫曼编码与解码
- C#关于异步调用的方法
- SpringMVC提交参数绑定list时,默认配置如果list大小超过256,就会报错
- 【简记】大规模Web开发技术(第四章)
- Java POI 导入Excel以及解析Excel
- Eclipse SVN插件比较 Subclipse vs Subversive
- 十四个方法提高博客的页面访问量
- linux Ubuntu16.04 oprofile1.1.0 编译笔记
- Java 集合Collection与List的详解
- 稳压二极管介绍和经典应用
- 疯狂JAVA讲义——第二章练习题
- 如何理解Python 中的 if __name__ == '__main__'
- C# AES加密算法