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

最新推荐文章于 2023-02-12 17:05:49 发布

YURUJUN

最新推荐文章于 2023-02-12 17:05:49 发布

阅读量751

点赞数

分类专栏：程序猿生涯

本文链接：https://blog.csdn.net/YURUJUN/article/details/38371021

版权

程序猿生涯专栏收录该内容

5 篇文章 0 订阅

订阅专栏

面试的时候，面试官肯定会问你在之前的公司参与过什么项目，你觉得在开发的过程中有哪些难点等等，为了避免在面试之前重新翻阅项目的代码，这里就将我之前参与的项目所用到的技术点进行归纳，总结。

首先所介绍的是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)) {// 2003
			System.err.println("读取excel2003文件内容");
			data = read2003Excel(file);
		} else if ("xlsx".equals(extension)) {// 2007
			System.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;
	}
}