java excel 框架图_java读取excle内容(类似表结构)

packagecom.test.word;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.OutputStream;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.CreationHelper;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFCellStyle;importorg.apache.poi.xssf.usermodel.XSSFDataFormat;importorg.apache.poi.xssf.usermodel.XSSFRichTextString;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** excle工具类

*

*@authorxieh 2019/11/24 使用的jar包是：poi-4.1.0.jar 和 poi-ooxml-4.1.0.jar

**/

public classExcel {/*** 此方法兼容.xls 和 .xlsx格式，建议使用

*@parampath excle文件路径

*@paramsheetIndex excle文件的Sheet页的下标，从0开始

*@returnexcle表中的内容

*@throwsException*/@SuppressWarnings("resource")public static List> excelXRead(String path,int sheetIndex) throwsException {//用流的方式先读取到你想要的excel的文件//FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));

File excel = newFile(path);

String[] split= excel.getName().split("\\."); //.是特殊字符，需要转义

Workbook wb = null;

FileInputStream fis= null;//根据文件后缀(xls/xlsx)进行判断

if ( "xls".equals(split[1])){

fis= new FileInputStream(excel); //文件流对象

wb = newHSSFWorkbook(fis);

}else if ("xlsx".equals(split[1])){

wb= newXSSFWorkbook(excel);

}else{

System.out.println("文件类型错误!");return new ArrayList<>();

}//开始解析

Sheet sheet = wb.getSheetAt(sheetIndex); //读取sheet 0//获取第一行

int firstrow = sheet.getFirstRowNum() + 1; //第一行是字段名，把第一行去掉//获取最后一行

int lastrow =sheet.getLastRowNum();//存取最后结果

List> result = new ArrayList>();//获取第一行(含有字段名)

Row rowFirst =sheet.getRow(sheet.getFirstRowNum());//循环行数依次获取列数

for (int i = firstrow; i < lastrow + 1; i++) {//获取哪一行i

Row row =sheet.getRow(i);//存放每行的键值对结果

Map tempMap = new HashMap();if (row != null) {//获取这一行的第一列

int firstcell =row.getFirstCellNum();//获取这一行的最后一列

int lastcell =row.getLastCellNum();//创建一个集合，用处将每一行的每一列数据都存入集合中//List list = new ArrayList();

for (int j = firstcell; j < lastcell; j++) {//获取第j列

Cell cell =row.getCell(j);if (cell != null && !("").equals(cell.toString())) {//System.out.print(cell + "\t");//list.add(cell.toString());

tempMap.put(rowFirst.getCell(j).toString(), cell.toString().trim());

}

}//存放每行的结果

result.add(tempMap);

}//row if end

}//for end

if(null !=fis){

fis.close();

}

System.out.println(result.toString());returnresult;

}/*** 创建和写入excle内容

*@paramexportList 实体类list

*@paramfile 生成的文件路径

*@throwsException*/

public static void writeXls(List exportList, File file) throwsException {

String[] options= { "ID", "内容", "名字"};

XSSFWorkbook book= newXSSFWorkbook();

CreationHelper createHelper=book.getCreationHelper();

XSSFCellStyle style=book.createCellStyle();

XSSFCellStyle dateStyle=book.createCellStyle();

XSSFDataFormat format=book.createDataFormat();

style.setWrapText(true);

dateStyle.setWrapText(true);

XSSFSheet sheet= book.createSheet("sheet");

sheet.setColumnWidth(3, 13000);

sheet.setDefaultColumnWidth(20);

XSSFRow firstRow= sheet.createRow(0);

XSSFCell[] firstCells= new XSSFCell[3];

CellStyle styleBlue= book.createCellStyle(); //样式对象//设置单元格的背景颜色为淡蓝色

styleBlue.setWrapText(true);//指定当单元格内容显示不下时自动换行

Font font=book.createFont();//font.setBoldweight(Font.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 280);

style.setFont(font);

dateStyle.setFont(font);

dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));

styleBlue.setFont(font);for (int j = 0; j < options.length; j++) {

firstCells[j]=firstRow.createCell(j);

firstCells[j].setCellStyle(styleBlue);

firstCells[j].setCellValue(newXSSFRichTextString(options[j]));

}

getExport(sheet, style, createHelper, exportList, dateStyle);if(file.exists()) {

file.delete();

}

file.createNewFile();

OutputStream os= newFileOutputStream(file);

book.write(os);

os.close();

}/*** excle实体数据写入

*@paramsheet

*@paramstyle

*@paramcreateHelper

*@paramexportList

*@paramdateStyle*/

private static void getExport(XSSFSheet sheet, XSSFCellStyle style, CreationHelper createHelper, ListexportList,

XSSFCellStyle dateStyle) {//遍历实例类的list集合

for (int i = 0; i < exportList.size(); i++) {//创建行

XSSFRow row = sheet.createRow(i + 1);//实体类

Test export =exportList.get(i);//第一列

XSSFCell hotelId = row.createCell(0);

hotelId.setCellStyle(style);//第二列

XSSFCell hotelName = row.createCell(1);

hotelName.setCellStyle(dateStyle);//第三列

XSSFCell chargeCount = row.createCell(2);

chargeCount.setCellStyle(style);//设置值

hotelId.setCellValue(export.getId());

hotelName.setCellValue("测试");

chargeCount.setCellValue(export.getName());//ta.append("写入excel开始,行数是" + (i + 1) + "\n");

}//for end

}/*** 测试

*

*@paramargs*/

public static voidmain(String[] args) {try{//List> excelXRead = Excel.excelXRead("E:/2.xlsx", 0);

List> excelXRead = Excel.excelXRead("E:/4.xls", 0);

System.out.println(excelXRead.toString());/*List show = new ArrayList();

Test test1 = new Test();

test1.setId(1);

test1.setName("xieh");

Test test2 = new Test();

test2.setId(2);

test2.setName("xieh");

show.add(test1);

show.add(test2);

Excel.writeXls(show, new File("D:/bbb.xls"));

Excel.writeXls(show, new File("D:/aaa.xlsx"));*/}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}