Java用poi导入导出Excel-白红宇的个人博客

Java用poi导入导出Excel

发布日期：2021-05-12 14:50:58 浏览次数：12 分类：精选文章

本文共 8665 字，大约阅读时间需要 28 分钟。

��

��Excel��Excel��Excel��Excel��

��Excel��

Apache POI�� -

easyExcel

��Apache POI��Excel��

2��Excel��

��Excel��

��

��Excel��

��

```javapublic void importExcel(@RequestParam("file") MultipartFile multipartFile) { File file = MultipartFile2FileUtil.MultipartFile2File(multipartFile);

try {    FileInputStream fs = FileUtils.openInputStream(file);    XSSFWorkbook workbook = new XSSFWorkbook(fs);        int numberOfSheets = workbook.getNumberOfSheets();    for (int j = 0; j < numberOfSheets; j++) {        XSSFRow hs = workbook.getSheetAt(j);        int first = hs.getFirstRowNum() + 1;        int last = hs.getLastRowNum();                List
   
     list = new ArrayList<>();                for (int i = first; i <= last; i++) {            XSSFRow row = hs.getRow(i);            if (!StringUtil.isNullOrEmpty(row.getCell(1).getStringCellValue())) {                list.add(new AuditContentImportBo(                    row.getCell(1).getStringCellValue(),                    // ... ������������                ));            }        }                String name = hs.getSheetName();        equipmentAuditContentService.importEquipmentAuditContent(name, list);    }    } catch (IOException e) {    e.printStackTrace();}MultipartFile2FileUtil.deleteFile(file);

}

���������
  ������������������MultipartFile2FileUtil������������������������������������������������������������������������������������
  ```javapublic class MultipartFile2FileUtil {    public static File MultipartFile2File(MultipartFile multipartFile) {        String fileName = multipartFile.getOriginalFilename();        File file = new File(fileName);        OutputStream out = null;                try {            out = new FileOutputStream(file);            byte[] ss = multipartFile.getBytes();            for (int i = 0; i < ss.length; i++) {                out.write(ss[i]);            }        } catch (IOException e) {            e.printStackTrace();        } finally {            if (out != null) {                try {                    out.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }                return file;    }        public static void deleteFile(File file) {        File f = new File(file.toURI());        f.delete();    }}

3��Excel

��Excel��

1. �� 2. ��Excel�� 3. ��Excel�� 4. ��Excel��

��

```javapublic ApiResult exportAuditContent(HttpServletResponse response, @RequestBody ExportAuditContentIds exportAuditContentIds) { OutputStream outputStream = tryCatch2gGetOutput(response); List

entitys = dao.getEntitys(); HSSFWorkbook workbook = new HSSFWorkbook();

String[] headers = {"������1", "������2", "������3"};int sheetNum = 0;for (Entity entity : entitys) {    List
    
      result = new ArrayList<>();        for (ExportAuditContent content : auditContent.getContentName()) {        List
     
       row = new ArrayList<>();        row.add(content.getXXX());        row.add(content.getXXX());        row.add(content.getXXX());        result.add(row);    }        ExcelForMoreSheet.exportExcel(        workbook,        sheetNum++,        entity.getSheetName(),        headers,        result,        outputStream    );}try {    workbook.write(outputStream);    outputStream.close();} catch (Exception e) {    throw new AnyException("������������");}return success("������������", "������");

}

���������
  ������������������������ExcelForMoreSheet������������������Excel������������������������������
  ```javapackage com.troo;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;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;import org.joda.time.DateTime;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;public class ExcelForMoreSheet {    public static void exportExcel(        HSSFWorkbook workbook,        int sheetNum,        String sheetTitle,        String[] headers,        List
    
     
      > result,        OutputStream outputStream    ) {        HSSFSheet sheet = workbook.createSheet();        workbook.setSheetName(sheetNum, sheetTitle);        sheet.setDefaultColumnWidth(20);                HSSFCellStyle style = workbook.createCellStyle();        style.setFillForegroundColor(HSSFColor.PALE_BLUE.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_CENTER);                HSSFFont font = workbook.createFont();        font.setColor(HSSFColor.BLACK.index);        font.setFontHeightInPoints(12);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        style.setFont(font);        style.setWrapText(true);                HSSFRow headerRow = sheet.createRow(0);        for (int i = 0; i < headers.length; i++) {            HSSFCell cell = headerRow.createCell(i);            cell.setCellStyle(style);            HSSFRichTextString text = new HSSFRichTextString(headers[i]);            cell.setCellValue(text.toString());        }                if (result != null) {            int index = 1;            for (List
      
        row : result) {                HSSFRow dataRow = sheet.createRow(index);                index++;                for (int i = 0; i < row.size(); i++) {                    HSSFCell cell = dataRow.createCell(i);                    if (row.get(i) == null) {                        row.add("");                    }                    cell.setCellValue(row.get(i).toString());                }            }        }    }}

4��03��07��

��Apache POI��Excel��

1. ��jar�� 2. 03��07��03��65536��07��

��

03��

```javaString PATH = "D:\\idea.webProject\\26";@Testpublic void testWrite03() throws Exception { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("��");

Row row1 = sheet.createRow(0);Cell cell11 = row1.createCell(0);cell11.setCellValue("������������");Cell cell12 = row1.createCell(1);cell12.setCellValue(666);Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("������������");Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);FileOutputStream fileOutputStream = new FileOutputStream(PATH + "zh03.xls");workbook.write(fileOutputStream);fileOutputStream.close();System.out.println("zh03 ���������������");

}

07���������
  ```java@Testpublic void testWrite07() throws IOException {    Workbook workbook = new XSSFWorkbook();    Sheet sheet = workbook.createSheet("���������");        Row row1 = sheet.createRow(0);    Cell cell11 = row1.createCell(0);    cell11.setCellValue("������������");        Cell cell12 = row1.createCell(1);    cell12.setCellValue("������������");        Row row2 = sheet.createRow(1);    Cell cell21 = row2.createCell(0);    cell21.setCellValue("������������");        Cell cell22 = row2.createCell(1);    String time = new DateTime().toString("yyyy-mm-dd HH:mm:ss");    cell22.setCellValue(time);        FileOutputStream fileOutputStream = new FileOutputStream(path + "java07.xlsx");    workbook.write(fileOutputStream);    fileOutputStream.close();        System.out.println("07������������������������");}

��

��Apache POI ��Excel��

上一篇：dfs-深度优先搜索（Depth-First-Search）

下一篇：Element UI组件介绍

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

��

2��Excel��

��

��

3��Excel

��

��

4��03��07��

03��

07��

��

发表评论

最新留言

关于作者

推荐文章

������

2���Excel������

������������

���������

3���������Excel

������������

���������

4���03������07������������

03���������

07���������

������

发表评论

最新留言

关于作者

推荐文章

��

2��Excel��

��

��

3��Excel

��

��

4��03��07��

03��

07��

��