项目所用技术回顾之excel导入（基于poi）

来源：互联网发布：以房养老骗局知乎编辑：程序博客网时间：2024/06/05 09:53
面试的时候，面试官肯定会问你在之前的公司参与过什么项目，你觉得在开发的过程中有哪些难点等等，为了避免在面试之前重新翻阅项目的代码，这里就将我之前参与的项目所用到的技术点进行归纳，总结。
首先所介绍的是excel导入--poi技术。
package com.poi.utils;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;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 com.poi.pojo.DataTable;public class ExcelUtil {public static void main(String args[]) {try {File excelFile = new File("E:/学生考试成绩.xlsx");List<DataTable> list = new ArrayList<DataTable>();list = readExcel(excelFile);} catch (Exception e) {e.printStackTrace();}}private static List<DataTable> readExcel(File file) throws Exception {List<DataTable> list = new ArrayList<DataTable>();String data[][] = null;String fileName = file.getName();String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);if ("xls".equals(extension)) {// 2003System.err.println("读取excel2003文件内容");data = read2003Excel(file);} else if ("xlsx".equals(extension)) {// 2007System.err.println("读取excel2007文件内容");data = read2007Excel(file);} else {throw new IOException("不支持的文件类型:" + extension);}for(int row=0;row<data.length;row++){for(int column=0;column<data[row].length;column++){System.out.print(data[row][column] + " ");}System.out.println("");}return list;}private static String[][] read2003Excel(File file) throws Exception{ArrayList<String[]> result = new ArrayList<String[]>();int rowSize = 0;InputStream fis = new FileInputStream(file);/**得到Exel工作簿*/HSSFWorkbook workBook = new HSSFWorkbook(fis);/**获取所有sheet页的数据*/for(int i=0;i<workBook.getNumberOfSheets();i++){HSSFSheet sheet = workBook.getSheetAt(i);HSSFRow row = null;HSSFCell cell = null;for(int rowIndex = 1;rowIndex<sheet.getPhysicalNumberOfRows();rowIndex++){row = sheet.getRow(rowIndex);int tempRowSize = row.getLastCellNum() + 1;                if(tempRowSize > rowSize){                    rowSize = tempRowSize;                }String[] values = new String[rowSize];Arrays.fill(values, "");for(int columnIndex = row.getFirstCellNum();columnIndex<row.getLastCellNum();columnIndex++){String value = "";cell = row.getCell(columnIndex);switch(cell.getCellType()){case HSSFCell.CELL_TYPE_BLANK:value = "";break;case HSSFCell.CELL_TYPE_BOOLEAN:value = (cell.getBooleanCellValue() == true ? "Y" : "N");break;case HSSFCell.CELL_TYPE_ERROR:value = "";break;case HSSFCell.CELL_TYPE_FORMULA:/** 如果是日期类型*/if(HSSFDateUtil.isCellDateFormatted(cell)){                                Date date = cell.getDateCellValue();                                if(date != null){                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);                                } else {                                    value = "";                                }                            } else {                                value = new DecimalFormat("0").format(cell.getNumericCellValue());                            }break;case HSSFCell.CELL_TYPE_NUMERIC:/** 如果是日期类型*/if(HSSFDateUtil.isCellDateFormatted(cell)){                                Date date = cell.getDateCellValue();                                if(date != null){                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);                                } else {                                    value = "";                                }                            } else {                                value = new DecimalFormat("0").format(cell.getNumericCellValue());                            }break;case HSSFCell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;default :break;}values[columnIndex] = value;}result.add(values);}}String[][] returnArray = new String[result.size()][rowSize];    for(int i = 0; i < returnArray.length; i++){        returnArray[i] = (String[]) result.get(i);    }return returnArray;}/** * 思路：将excel里面的数据以二维字符串存储,得到数据后,根据对象字段的类型进行转换 * @param file * @return * @throws Exception */private static String[][] read2007Excel(File file) throws Exception{ArrayList<String[]> result = new ArrayList<String[]>();int rowSize = 0;InputStream fis = new FileInputStream(file);/**得到Exel工作簿*/XSSFWorkbook workBook = new XSSFWorkbook(fis);/**获取所有sheet页的数据*/for(int i=0;i<workBook.getNumberOfSheets();i++){XSSFSheet sheet = workBook.getSheetAt(i);XSSFRow row = null;XSSFCell cell = null;for(int rowIndex = 1;rowIndex<sheet.getPhysicalNumberOfRows();rowIndex++){row = sheet.getRow(rowIndex);int tempRowSize = row.getLastCellNum() + 1;                if(tempRowSize > rowSize){                    rowSize = tempRowSize;                }String[] values = new String[rowSize];Arrays.fill(values, "");for(int columnIndex = row.getFirstCellNum();columnIndex<row.getLastCellNum();columnIndex++){String value = "";cell = row.getCell(columnIndex);switch(cell.getCellType()){case XSSFCell.CELL_TYPE_BLANK:value = "";break;case XSSFCell.CELL_TYPE_BOOLEAN:value = (cell.getBooleanCellValue() == true ? "Y" : "N");break;case XSSFCell.CELL_TYPE_ERROR:value = "";break;case XSSFCell.CELL_TYPE_FORMULA:/** 如果是日期类型*/if(HSSFDateUtil.isCellDateFormatted(cell)){                                Date date = cell.getDateCellValue();                                if(date != null){                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);                                } else {                                    value = "";                                }                            } else {                                value = new DecimalFormat("0").format(cell.getNumericCellValue());                            }break;case XSSFCell.CELL_TYPE_NUMERIC:/** 如果是日期类型*/if(HSSFDateUtil.isCellDateFormatted(cell)){                                Date date = cell.getDateCellValue();                                if(date != null){                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);                                } else {                                    value = "";                                }                            } else {                                value = new DecimalFormat("0").format(cell.getNumericCellValue());                            }break;case XSSFCell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;default :break;}values[columnIndex] = value;}result.add(values);}}String[][] returnArray = new String[result.size()][rowSize];    for(int i = 0; i < returnArray.length; i++){        returnArray[i] = (String[]) result.get(i);    }return returnArray;}}
0 0