package com.supren.utils;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
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 org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
//导入
public static List<List<Object>> importExcel(InputStream in,String filename) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
org.apache.poi.ss.usermodel.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 = ((org.apache.poi.ss.usermodel.Sheet) sheet).getFirstRowNum(); j <=((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum(); j++) {
row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
System.out.println(row.getFirstCellNum()+" "+row.getLastCellNum()+" "+sheet.getLastRowNum());
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getCellValue(cell));
}
if(!li.get(0).equals("")){
list.add(li);
}
}
}
work.close();
return list;
}
public static 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;
}
public static 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("yyyy/m/d;@".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 = "";
System.out.println("空数据");
break;
default:
break;
}
return value;
}
//导出
/*
* Workbookname 工作簿名称
* sheetName 表名称
* title 第一行标题
* object 表中数据
*/
public static void Excelexport(String Workbookname,String sheetName,List<String> title,List<List<String>> object) throws Exception {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
for(int i=0;i<title.size();i++){
cell = row.createCell((short) i);
cell.setCellValue(title.get(i));
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
for (int i = 0; i < object.size(); i++)
{
row = sheet.createRow((int) i + 1);
// 第四步,创建单元格,并设置值
for(int j=0;j<object.get(i).size();j++){
System.out.println(object.get(i).get(j));
row.createCell((short) j).setCellValue(object.get(i).get(j));
}
}
// 第六步,将文件存到指定位置
try
{
FileOutputStream fout = new FileOutputStream("E:/"+Workbookname+".xlsx");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
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 org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
//导入
public static List<List<Object>> importExcel(InputStream in,String filename) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = getWorkbook(in, filename);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
org.apache.poi.ss.usermodel.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 = ((org.apache.poi.ss.usermodel.Sheet) sheet).getFirstRowNum(); j <=((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum(); j++) {
row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
System.out.println(row.getFirstCellNum()+" "+row.getLastCellNum()+" "+sheet.getLastRowNum());
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getCellValue(cell));
}
if(!li.get(0).equals("")){
list.add(li);
}
}
}
work.close();
return list;
}
public static 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;
}
public static 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("yyyy/m/d;@".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 = "";
System.out.println("空数据");
break;
default:
break;
}
return value;
}
//导出
/*
* Workbookname 工作簿名称
* sheetName 表名称
* title 第一行标题
* object 表中数据
*/
public static void Excelexport(String Workbookname,String sheetName,List<String> title,List<List<String>> object) throws Exception {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
for(int i=0;i<title.size();i++){
cell = row.createCell((short) i);
cell.setCellValue(title.get(i));
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
for (int i = 0; i < object.size(); i++)
{
row = sheet.createRow((int) i + 1);
// 第四步,创建单元格,并设置值
for(int j=0;j<object.get(i).size();j++){
System.out.println(object.get(i).get(j));
row.createCell((short) j).setCellValue(object.get(i).get(j));
}
}
// 第六步,将文件存到指定位置
try
{
FileOutputStream fout = new FileOutputStream("E:/"+Workbookname+".xlsx");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}