Java读取和写入excel

﹏ヽ暗。殇╰゛Y 2024-04-19 13:08 93阅读 0赞

*  excel读取

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    
    public class ExcelRead {
    
        private Workbook workbook;//workbook = WorkbookFactory.create(is);这种方式 Excel 2003/2007/2010 都是可以处理的
        private Sheet sheet;
        private FormulaEvaluator formulaEvaluator;
    
        public ExcelRead(String filePath) {
            File file = new File(filePath);
            try {
                InputStream is = new FileInputStream(file);
                if (file.getName().endsWith("xlsx")) {
                    workbook = new XSSFWorkbook(is);
                    formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                } else {
                    workbook = new HSSFWorkbook(is);
                    formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                }
            } catch (Exception e) {
                System.out.println("错误 : " + e);
            }
        }
    
    
        /**
         * 获取合并单元格的值
         *
         * @param sheet
         * @param row
         * @param column
         * @return
         */
        public String getMergedRegionValue(Sheet sheet, int row, int column) {
            int sheetMergeCount = sheet.getNumMergedRegions();
    
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
    
                if (row >= firstRow && row <= lastRow) {
    
                    if (column >= firstColumn && column <= lastColumn) {
                        Row fRow = sheet.getRow(firstRow);
                        Cell fCell = fRow.getCell(firstColumn);
                        return getCellValue(fCell);
                    }
                }
            }
    
            return null;
        }
    
        /**
         * 判断指定的单元格是否是合并单元格
         *
         * @param sheet
         * @param row    行下标
         * @param column 列下标
         * @return
         */
        private boolean isMergedRegion(Sheet sheet, int row, int column) {
            int sheetMergeCount = sheet.getNumMergedRegions();
    
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
    
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        return true;
                    }
                }
            }
            return false;
        }
    
        /**
         * 获取单元格的值
         *
         * @param cell
         * @return
         */
        public String getCellValue(Cell cell) {
            DataFormatter formatter = new DataFormatter();
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:// 数字
                        if (DateUtil.isCellDateFormatted(cell)) {
                            return formatter.formatCellValue(cell);
                        } else if (cell.getCellStyle().getDataFormatString().equals("reserved-0x1f")) {
                            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
                            return fmt.format(cell.getDateCellValue());
                        } else if (cell.getCellStyle().getDataFormatString().equals("reserved-0x1c")) {
                            SimpleDateFormat fmt = new SimpleDateFormat("MM-dd");
                            return fmt.format(cell.getDateCellValue());
                        } else {
                            double value = cell.getNumericCellValue();
                            int intValue = (int) value;
                            return value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                        }
                    case Cell.CELL_TYPE_STRING: // 字符串
                        return cell.getStringCellValue();
                    case Cell.CELL_TYPE_BOOLEAN: // Boolean
                        return String.valueOf(cell.getBooleanCellValue());
                    case Cell.CELL_TYPE_FORMULA: // 公式
                        return String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
                    case Cell.CELL_TYPE_BLANK: // 空值
                        return "";
                    case Cell.CELL_TYPE_ERROR: // 故障
                        return "";
                    default:
                        return "UNKNOWN TYPE";
                }
            } else {
                return "";
            }
        }
    
        //读取表格中的内容
        public List<List<String>> getCellcontent() {
            List<List<String>> excelArray = new ArrayList<>();
            for (Row row : sheet) {
                List<String> list = new ArrayList<>();
                int end = row.getLastCellNum();
                for (int i = 0; i < end; i++) {
                    Cell cell = row.getCell(i);
                    try {
                        if (this.isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex())) {
                            list.add(this.getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex()));
                        } else {
                            list.add(this.getCellValue(cell));
                        }
                    }catch (NullPointerException e){
                        list.add(this.getCellValue(cell));
                    }
    
                }
                excelArray.add(list);
            }
            return excelArray;
        }
    
        //读取指定sheet表格的内容
        public List<List<String>> getSheetValue(int sheetValue) {
            sheet = workbook.getSheetAt(sheetValue);
            List<List<String>> lists = this.getCellcontent();
            return lists;
        }
    
        //读取多个sheet表格的内容
        public HashMap<String, List<List<String>>> getSheetValues() {
            HashMap<String, List<List<String>>> sheetMap = new HashMap<>();
            int sheetCount = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                sheet = workbook.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                sheetMap.put(sheetName, this.getCellcontent());
            }
            return sheetMap;
        }
    
        public static void main(String[] args) {
            ExcelRead excelRead = new ExcelRead("path\demo.xlsx");
            System.out.println(excelRead.getSheetValue(0));
        }
    }

*  excel写入

import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    
    
    public class ExcelWrite {
        /**
         * 导出excel文件
         * @param title  表sheet的名字
         * @param headers  表头
         * @param dataList  正文单元格
         * @param out  输出流
         */
        public void exporteExcel(String title,String[] headers,String[][] dataList,OutputStream out){
            HSSFWorkbook workBook = new HSSFWorkbook();
            createSheet(title, headers, dataList, workBook);
            createSheet(title+"2", headers, dataList, workBook);
            try {
                workBook.write(out);
            }catch (IOException e){
                System.out.println("写入文件失败"+e.getMessage());
            }
        }
    
        /**
         * 创建sheet
         * @param title  sheet的名字
         * @param headers  表头
         * @param dataList  正文单元格
         */
        private void createSheet(String title, String[] headers, String[][] dataList, HSSFWorkbook workBook) {
            HSSFSheet sheet = workBook.createSheet(title);
    //        sheet.setDefaultColumnWidth(15);
            //设置表头和普通单元格的格式
            HSSFCellStyle headStyle = setHeaderStyle(workBook);
            HSSFCellStyle bodyStyle = setBodyStyle(workBook);
    
            createBody(dataList, sheet, bodyStyle);
            createHeader(headers, sheet, headStyle);
        }
    
        /**
         * 创建正文单元格
         * @param dataList 数据数组
         * @param sheet 表
         * @param bodyStyle 单元格格式
         */
        private void createBody(String[][] dataList, HSSFSheet sheet, HSSFCellStyle bodyStyle) {
            for (int a=0;a<dataList.length;a++){
                HSSFRow row = sheet.createRow(a+1);
                for(int j=0;j<dataList[a].length;j++){
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(bodyStyle);
                    HSSFRichTextString textString = new HSSFRichTextString(dataList[a][j]);
                    cell.setCellValue(textString);
                }
            }
        }
    
        /**
         * 创建表头
         * @param headers  表头
         * @param sheet 表
         * @param headStyle 表头格式
         */
        private void createHeader(String[] headers, HSSFSheet sheet, HSSFCellStyle headStyle) {
            HSSFRow row = sheet.createRow(0);
            for (int i=0;i<headers.length;i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(headStyle);
                HSSFRichTextString textString = new HSSFRichTextString(headers[i]);
                cell.setCellValue(textString);
                sheet.autoSizeColumn((short)i);
            }
        }
    
        /**
         * 设置正文单元格格式
         * @param workBook
         * @return
         */
        private HSSFCellStyle setBodyStyle(HSSFWorkbook workBook) {
            HSSFCellStyle style2 = workBook.createCellStyle();
            style2.setFillForegroundColor(HSSFColor.WHITE.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    
            HSSFFont font2 = workBook.createFont();
            font2.setFontName("微软雅黑");
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            style2.setFont(font2);
            return style2;
        }
    
        /**
         * 设置表头格式
         * @param workBook
         * @return
         */
        private HSSFCellStyle setHeaderStyle(HSSFWorkbook workBook) {
            HSSFCellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    
            HSSFFont font = workBook.createFont();
            font.setFontName("微软雅黑");
            font.setFontHeightInPoints((short)12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            return style;
        }
    
        public void writeExcel(String excelName,String[] headers,String[][] excelContent,String title){
            try {
                String path = System.getProperty("user.dir");
                OutputStream os = new FileOutputStream(path+"/"+excelName);
                /*Date date = new Date(System.currentTimeMillis());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(date);*/
                this.exporteExcel(title,headers,excelContent,os);
                os.close();
            }catch (FileNotFoundException e){
                System.out.println("无法找到文件");
            }catch (IOException e){
                System.out.println("写入文件失败");
            }
        }
    
        public static void main(String[] args) {
            WriteExcel writeExcel = new WriteExcel();
            try {
                OutputStream os = new FileOutputStream("path/demo.xlsx");
                String[] headers={"无敌的含义"};
                /*Date date = new Date(System.currentTimeMillis());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(date);*/
                String [][] list={
       {"hehe","yeyeyey"},{"haha"}};
                writeExcel.exporteExcel("学生表",headers,list,os);
                os.close();
            }catch (FileNotFoundException e){
                System.out.println("无法找到文件");
            }catch (IOException e){
                System.out.println("写入文件失败");
            }
        }
    }