admin 管理员组文章数量: 894082
Java代码实现excel的导入和导出
文章目录
- 简介
- 常用方法
- 导出Excel
- 导入Excel
简介
目前比较常用的实现Java导入、导出Excel的技术有:Jakarta POI、freemarker和Java Excel,在此只对Jakarta POI做简单的讲解
.html
常用方法
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页:.html ,API文档: .html
- 遍历workbook
// load源文件 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath)); HSSFWorkbook wb = new HSSFWorkbook(fs); for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i ++) { HSSFRow row = sheet.getRow(i); if (row != null) { 。。。操作} } } // 目标文件 FileOutputStream fos = new FileOutputStream(objectPath); //写文件 swb.write(fos); fos.close();
- 得到列和单元格
HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell((short) j);
- 设置sheet名称和单元格内容为中文
wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16); cell.setEncoding((short) 1); cell.setCellValue("中文");
- 单元格内容未公式或数值,可以这样读写
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.getNumericCellValue()
- 设置列宽、行高
sheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height);
- 添加区域,合并单元格
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo); sheet.addMergedRegion(region); //得到所有区域 sheet.getNumMergedRegions()
- 常用方法
根据单元格不同属性返回字符串数值
public String getCellStringValue(HSSFCell cell) { String cellValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); if(cellValue.trim().equals("")||cellValue.trim().length()<=0) cellValue=" "; break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue=" "; break; case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } return cellValue; }
- 常用单元格边框格式
虚线HSSFCellStyle.BORDER_DOTTED
实线HSSFCellStyle.BORDER_THIN
public static HSSFCellStyle getCellStyle(short type) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(type);//下边框 style.setBorderLeft(type);//左边框 style.setBorderRight(type);//右边框 style.setBorderTop(type);//上边框 return style; }
- 设置字体和内容位置
HSSFFont f = wb.createFont(); f.setFontHeightInPoints((short) 11);//字号 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style.setFont(f); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 style.setRotation(short rotation);//单元格内容的旋转的角度 HSSFDataFormat df = wb.createDataFormat(); style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式 cell.setCellFormula(string);//给单元格设公式 style.setRotation(short rotation);//单元格内容的旋转的角度 cell.setCellStyle(style);
10.插入图片
//先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); ImageIO.write(bufferImg,"jpg",byteArrayOut); //读进一个excel模版 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt"); fs = new POIFSFileSystem(fos); //创建一个工作薄 HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
导出Excel
- 环境配置(导入jar包)
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency>
- Jakarta POI HSSF API组件
HSSF(用于操作Excel的组件)提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象,样式和格式,还有辅助操作。有以下几种对象:
常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾(只有打印的时候才能看到效果)
样式:
HSSFCellStyle cell样式
辅助操作包括:
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
-
基本操作步骤
首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。
基本操作步骤:
a、用HSSFWorkbook打开或者创建“Excel文件对象”
b、用HSSFWorkbook对象返回或者创建Sheet对象
c、用Sheet对象返回行对象,用行对象得到Cell对象
d、对Cell对象读写。 -
测试用例(暂定)
import java.io.FileOutputStream;
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.HSSFFont;
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.hssf.util.Region;public class POIExportExcel {public HSSFWorkbook generateExcel1(List list, HSSFWorkbook wb, String title) {//行的数量int rows = 0;HSSFSheet sheet = wb.createSheet("Sheet1");// 数据格样式HSSFCellStyle dataCellStyle = createDataCellStyle(wb);// 小标题样式HSSFCellStyle cellStyle = createCellStyle(wb);sheet.setDefaultRowHeight((short) 400);//设置所有行高为400//设置列宽sheet.setColumnWidth((short) 1, (short) 2800);sheet.setColumnWidth((short) 2, (short) 2800);sheet.setColumnWidth((short) 3, (short) 2800);sheet.setColumnWidth((short) 4, (short) 2800);sheet.setColumnWidth((short) 5, (short) 2800);sheet.setColumnWidth((short) 6, (short) 2800);sheet.setColumnWidth((short) 7, (short) 2800);sheet.setColumnWidth((short) 8, (short) 2800);sheet.setColumnWidth((short) 9, (short) 2800);sheet.setColumnWidth((short) 10, (short) 3200);sheet.setColumnWidth((short) 11, (short) 3200);sheet.setColumnWidth((short) 12, (short) 3200);sheet.setColumnWidth((short) 13, (short) 3200);//标题行HSSFRow row1 = sheet.createRow((short) (rows++));row1.setHeight((short) 500);sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 13));//横跨1-14列createCell(row1, (short) 0, cellStyle, title);//表头行(1,2列合并;3,4,5,6,7列合并;8,9列合并)HSSFRow row2 = sheet.createRow((short) (rows++));sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 6));sheet.addMergedRegion(new Region(1, (short) 7, 1, (short) 8));sheet.addMergedRegion(new Region(1, (short) 9, 1, (short) 9));sheet.addMergedRegion(new Region(1, (short) 10, 1, (short) 10));sheet.addMergedRegion(new Region(1, (short) 11, 1, (short) 11));sheet.addMergedRegion(new Region(1, (short) 12, 1, (short) 12));sheet.addMergedRegion(new Region(1, (short) 13, 1, (short) 13));createCell(row2, (short) 0, dataCellStyle, "name1");createNullCell(row2, (short) 1, dataCellStyle);createCell(row2, (short) 2, dataCellStyle, "name2");createNullCell(row2, (short) 3, dataCellStyle);createNullCell(row2, (short) 4, dataCellStyle);createNullCell(row2, (short) 5, dataCellStyle);createNullCell(row2, (short) 6, dataCellStyle);createCell(row2, (short) 7, dataCellStyle, "name3");createNullCell(row2, (short) 8, dataCellStyle);createCell(row2, (short) 9, dataCellStyle, "name4");createCell(row2, (short) 10, dataCellStyle, "name5");createCell(row2, (short) 11, dataCellStyle, "name6");createCell(row2, (short) 12, dataCellStyle, "name7");createCell(row2, (short) 13, dataCellStyle, "name8");//遍历集合,保存数据到单元格for(int i=0;i<list.size();i++){Object javaBean = list.get(i);//具体的数据值通过对象去获取,这里简单给值String variable1 = "value1"+"_"+i;String variable2 = "value2"+"_"+i;String variable3 = "value3"+"_"+i;String variable4 = "value4"+"_"+i;String variable5 = "value5"+"_"+i;String variable6 = "value6"+"_"+i;String variable7 = "value7"+"_"+i;String variable8 = "value8"+"_"+i;HSSFRow rowi = sheet.createRow((short) (rows++));sheet.addMergedRegion(new Region(i+2, (short) 0, i+2, (short) 1));sheet.addMergedRegion(new Region(i+2, (short) 2, i+2, (short) 6));sheet.addMergedRegion(new Region(i+2, (short) 7, i+2, (short) 8));sheet.addMergedRegion(new Region(i+2, (short) 9, i+2, (short) 9));sheet.addMergedRegion(new Region(i+2, (short) 10, i+2, (short) 10));sheet.addMergedRegion(new Region(i+2, (short) 11, i+2, (short) 11));sheet.addMergedRegion(new Region(i+2, (short) 12, i+2, (short) 12));sheet.addMergedRegion(new Region(i+2, (short) 13, i+2, (short) 13));createCell(rowi, (short) 0, dataCellStyle, variable1);createNullCell(rowi, (short) 1, dataCellStyle);createCell(rowi, (short) 2, dataCellStyle, variable2);createNullCell(rowi, (short) 3, dataCellStyle);createNullCell(rowi, (short) 4, dataCellStyle);createNullCell(rowi, (short) 5, dataCellStyle);createNullCell(rowi, (short) 6, dataCellStyle);createCell(rowi, (short) 7, dataCellStyle, variable3);createNullCell(rowi, (short) 8, dataCellStyle);createCell(rowi, (short) 9, dataCellStyle, variable4);createCell(rowi, (short) 10, dataCellStyle, variable5);createCell(rowi, (short) 11, dataCellStyle, variable6);createCell(rowi, (short) 12, dataCellStyle, variable7);createCell(rowi, (short) 13, dataCellStyle, variable8);}return wb;}/*** 创建单元格* @param row* @param id* @param style* @param value*/private void createCell(HSSFRow row, short id, HSSFCellStyle style,String value) {HSSFCell cell = row.createCell((short) id);// cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(value);cell.setCellStyle(style);}/*** 创建空的单元格*/private HSSFCell createNullCell(HSSFRow row, short id, HSSFCellStyle style) {HSSFCell null1Cell = row.createCell(id);null1Cell.setCellStyle(style);return null1Cell;}/*** 设置数据单元格样式* @param wb* @return*/private HSSFCellStyle createDataCellStyle(HSSFWorkbook wb) {HSSFCellStyle dataCellStyle = wb.createCellStyle();// 水平居中dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);return dataCellStyle;}/*** 设置单元格样式* @param wb* @return*/private HSSFCellStyle createCellStyle(HSSFWorkbook wb) {// 小标题样式HSSFCellStyle cellStyle = wb.createCellStyle();// 水平居中cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 字体HSSFFont littleFont = wb.createFont();// 设置字体为粗体littleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 然后将字体关联到样式cellStyle.setFont(littleFont);cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);return cellStyle;}public static void main(String[] args){/*String serverPath = request.getSession().getServletContext().getRealPath("/")+"exportExcel"; //在服务器端创建文件夹 File file = new File(serverPath+path); if(!file.exists()){ file.mkdir(); } SimpleDateFormat sfm = new SimpleDateFormat("yyyy-MM-dd"); String filename = "excel" + "_" + sfm.format(new java.util.Date()); String encodedfileName = new String(filename.getBytes(), "GBK"); FileOutputStream out = new FileOutputStream(serverPath+path+"/"+encodedfileName+".xls"); */ new POIExportExcel();String title = "POI导出excel";List list = new ArrayList<String>();//加入两条做个测试list.add(new String());list.add(new String());try {FileOutputStream out = new FileOutputStream("D:/"+title+".xls");HSSFWorkbook wb =new HSSFWorkbook();wb = new POIExportExcel().generateExcel1(list,wb,title);wb.write(out);/*List srcfile=new ArrayList(); srcfile.add(new File(serverPath+path+"/"+encodedfileName+".xls")); //将服务器上存放Excel的文件夹打成zip包 File zipfile = new File(serverPath+path+".zip"); ZipUtil.zipFiles(srcfile, zipfile); //弹出下载框供用户下载 this.downFile(response,serverPath, path+".zip");*/} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}
}
导入Excel
读取Excel文件时,首先生成一个POIFSFileSystem对象,由POIFSFileSystem对象构造一个HSSFWorkbook,该HSSFWorkbook对象就代表了Excel文档。
- 引入jar包
包括dom4j.jar和xmlbeans.jar
没有的话会抛出:Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
<dependency><groupId>dom4j</groupId><artifactId>dom4j</artifactId><version>1.4</version></dependency>
- 例子1、
import java.io.File;
import java.io.FileInputStream;
import java.sql.Date;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class MyExcelReader {/*** 分析获取上传的excel文件中的数据信息。 仅对Excel文件的第一张表进行分析,分析过程中如果遇到空行就结束。* @param file* excel* @return excel中对应的数据以二维数组形式返回* @throws Exception*/private String[][] readExcel(File file) {String[][] data = null;Workbook wb = null;try {// 解析excel2003if (file.getName().endsWith(".xls")) {POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file.getPath()));// excel数据对象wb = new HSSFWorkbook(fs);}// 解析excel2007及以上if (file.getName().endsWith(".xlsx")) {wb = new XSSFWorkbook(new FileInputStream(file.getPath()));}// excel中对应的表单Sheet sheet = wb.getSheetAt(0);// excel中的有效列数int colNum = getValidColNum(sheet);// 只有excel的有效列数大于零才对数据进行分析if (colNum > 0) {// 从Excel中获取到的有效数据data = getExcelData(sheet, colNum);}} catch (Exception e) {e.printStackTrace();}return data;}/*** 得到表单的有效列数. 获取表单第一行开始位置连续非空单元格的个数* * @param sheet* Excel的表单* @return 列数* @throws Exception*/private int getValidColNum(Sheet sheet) throws Exception {// 判断文件是否为空文件if (sheet.getRow(0) == null) {System.out.println("文件为空");throw new Exception("error.importExcel.blankExcelFile");}// 表单总列数int colNum = sheet.getRow(0).getPhysicalNumberOfCells();Row row = sheet.getRow(0);// 如果表单第一行即表头位置出现空单元格,该单元格后面的信息不在导入for (int i = 0; i < colNum; i++) {if (row.getCell((short) i) != null) {Cell cell = row.getCell((short) i);String value = this.getCellValue(cell);if (StringUtils.isEmpty(value)) {colNum = i;break;}}// 如果列表头信息为空,不读取后面的数据else {colNum = i;}}return colNum;}/*** 读取Excel数据. 读取过程中,遇到空行自动停止,舍弃空行后面的数据。 可根据数组信息获取Excel的有效行数* * @param sheet* 表单对象* @param colNum* 有效列数* @return 数据二维数组*/private String[][] getExcelData(Sheet sheet, int colNum) {int tempRowNum = sheet.getPhysicalNumberOfRows();// 存储从Excel表中读取的数据信息List<List<String>> dataList = new ArrayList<List<String>>();// 读取Excel数据,将其存储在可变长的List容器中,同时得到Excel文件的有效数据行数int rowNum = 0;for (; rowNum < tempRowNum && sheet.getRow(rowNum) != null; rowNum++) {List<String> rowData = new ArrayList<String>();// 对行信息进行累加,判断该行是否为空行String rowValue = "";for (int j = 0; j < colNum; j++) {if (sheet.getRow(rowNum).getCell((short) j) != null) {Cell cell = sheet.getRow(rowNum).getCell((short) j);String value = getCellValue(cell);rowData.add(value);rowValue += value;} else {rowData.add(null);}}// 读取信息时如遇到空行,结束读入if (rowValue.length() == 0) {break;} else {dataList.add(rowData);}}// 将Excel数据转存到数组对象中String[][] data = new String[rowNum][colNum];for (int i = 0; i < dataList.size(); i++) {for (int j = 0; j < dataList.get(i).size(); j++) {data[i][j] = dataList.get(i).get(j);}}return data;}/*** 得到Excel单元格的数据内容* * @param cell* Excel单元格对象* @return 单元格的内容*/public String getCellValue(Cell cell) {// excel的日期格式和java的有很大区别if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC&& DateUtil.isCellDateFormatted(cell)) {return new Date(cell.getDateCellValue().getTime()).toString();} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {double value = cell.getNumericCellValue();return new DecimalFormat("###.####").format(value);} else {String result = "";try {RichTextString textString = cell.getRichStringCellValue();if (textString != null) {result = textString.getString().trim();result = this.subStr(result);if (result.length() > 0) {char firstChar = result.charAt(0);// 如果信息不是以英文字符或数字开始,替换掉信息中的所有中英文空格,以英文或数字开始的信息不处理if (!(firstChar >= 'a' && firstChar <= 'z'|| firstChar >= 'A' && firstChar <= 'Z' || firstChar >= '0'&& firstChar <= '9')) {result = result.replaceAll(" ", "").replaceAll(" ","");}}}} catch (Exception e) {e.printStackTrace();}return result;}}/*** 截取字符串中的内码* * @param str* @return*/public String subStr(String str) {for (int i = 0; i < str.length(); i++) {// 韩文等乱码,均以&#开头以;结束的字符if (str.indexOf("&#") >= 0&& str.indexOf(";", str.indexOf("&#")) > 0) {String s2 = str.substring(str.indexOf("&#"), str.indexOf(";",str.indexOf("&#")) + 1);s2 = this.cov(s2);str = str.substring(0, str.indexOf("&#"))+ s2+ str.substring(str.indexOf(";", str.indexOf("&#")) + 1, str.length());}}return str;}/*** 转化编码,韩文等乱码* * @param string* @return*/public String cov(String string) {String str = string.replaceAll("&#", ",").replaceAll(";", "");String[] s2 = str.split(",");String s1 = "";for (int i = 1; i < s2.length; i++) {int a = Integer.parseInt(s2[i], 10);s1 = s1 + (char) a;}return s1;}/*** 测试* @param args*/public static void main(String[] args) {File file = new File("D:/test.xlsx");// 保存Excel文件到服务器String[][] dataArray = new MyExcelReader().readExcel(file);// System.out.println("标题:"+dataArray[0].toString());for (int i = 0; i < dataArray.length; i++) {String[] rowData = dataArray[i];for (int j = 0; j < rowData.length; j++) {System.out.println("第" + (i + 1) + "行第" + (j + 1) + "列:"+ dataArray[i][j]);}}}
}
- 例子2、
/**
*将导入的excel表,转化成一个List集合
**/
public class POIUtil {private static Logger logger = LoggerFactory.getLogger(POIUtil.class);private final static String xls = "xls";private final static String xlsx = "xlsx";/*** 读入excel文件,解析后返回* @param file* @param lastCellNum 一行数据的个数。* @throws IOException*/public static List<String[]> readExcel(MultipartFile file,int lastCellNum) throws IOException {//检查文件checkFile(file);//获得Workbook工作薄对象Workbook workbook = getWorkBook(file);//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回List<String[]> list = new ArrayList<String[]>();if(workbook != null){int sheetsNums = workbook.getNumberOfSheets();for(int sheetNum = 0;sheetNum < sheetsNums;sheetNum++){//获得当前sheet工作表Sheet sheet = workbook.getSheetAt(sheetNum);if(sheet == null){continue;}//获得当前sheet的开始行int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();//循环除了第一行的所有行for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){//获得当前行Row row = sheet.getRow(rowNum);if(row == null){continue;}//获得当前行的开始列int firstCellNum = row.getFirstCellNum();//获得当前行的列数
// int lastCellNum = row.getPhysicalNumberOfCells();String[] cells = new String[lastCellNum];//循环当前行for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){if (cellNum < 0) {break;}Cell cell = row.getCell(cellNum);cells[cellNum] = getCellValue(cell);}//如果某一行都为空,则该行都为空,不插入boolean isEmpty = true;for (int i = 0; i < lastCellNum; i++) {if (!StringUtils.isBlank(cells[i])) {isEmpty = false;}}if (!isEmpty) {list.add(cells);}}}}return list;}private static void checkFile(MultipartFile file) throws IOException{//判断文件是否存在if(null == file){logger.error("文件不存在!");throw new FileNotFoundException("文件不存在!");}//获得文件名String fileName = file.getOriginalFilename();//判断文件是否是excel文件if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){logger.error(fileName + "不是excel文件");throw new IOException(fileName + "不是excel文件");}}private static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName = file.getOriginalFilename();//创建Workbook工作薄对象,表示整个excelWorkbook workbook = null;try {//获取excel文件的io流@Cleanup InputStream is = file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if(fileName.endsWith(xls)){//2003workbook = new HSSFWorkbook(is);}else if(fileName.endsWith(xlsx)){//2007workbook = new XSSFWorkbook(is);}} catch (IOException e) {logger.info(e.getMessage());}return workbook;}private static String getCellValue(Cell cell){String cellValue = "";if(cell == null){return "";}//把数字当成String来读,避免出现1读成1.0的情况if(cell.getCellType() == CellType.NUMERIC){cell.setCellType(CellType.STRING);}//判断数据的类型switch (cell.getCellType()){//数字case NUMERIC:cellValue = String.valueOf(cell.getNumericCellValue());break;//字符串case STRING:cellValue = String.valueOf(cell.getStringCellValue());break;//Booleancase BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());break;//公式case FORMULA:cellValue = String.valueOf(cell.getCellFormula());break;//空值case BLANK:cellValue = "";break;//故障case ERROR:cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}
}
本文标签: Java代码实现excel的导入和导出
版权声明:本文标题:Java代码实现excel的导入和导出 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.freenas.com.cn/jishu/1688190421h190053.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论