面试的时候,面试官肯定会问你在之前的公司参与过什么项目,你觉得在开发的过程中有哪些难点等等,为了避免在面试之前重新翻阅项目的代码,这里就将我之前参与的项目所用到的技术点进行归纳,总结。
首先所介绍的是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;
}
}