java实现创建通用创建Excel

本人使用的是maven源管理jar包，所以第一步导入所需要的jar包，在pom.xml文件中添加

	<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.11-beta2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.11-beta2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.11-beta2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-excelant</artifactId>
			<version>3.11-beta2</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
		<dependency>
		    <groupId>org.apache.xmlbeans</groupId>
		    <artifactId>xmlbeans</artifactId>
		    <version>2.3.0</version>
		</dependency>
				

1.判断文件是否存在

 FileOutputStream out = null;
 // 声明一个工作薄
 XSSFWorkbook workbook = null;

2.创建一个表格，其中excelPath,title是方法中传入的参数，简称形参，下面方法传入了三个sheet页

   out = new FileOutputStream(excelPath);
            workbook = new XSSFWorkbook();
            // 生成一个表格
            XSSFSheet sheet = workbook.createSheet(title);
            XSSFSheet volSheet=workbook.createSheet("title2");
            XSSFSheet temSheet=workbook.createSheet("title3");

3.生成第一行作为头部，就是每列的列名，每个sheet页面分别生成

   //2、生成第一行，作为头部
            XSSFRow row = sheet.createRow(0);

            XSSFRow volRow = volSheet.createRow(0);
   
            XSSFRow temRow= temSheet.createRow(0);

4.第一个sheet页生成每列列名，cloumns数组是形参 

  XSSFCell cellHeader;
            for (int i = 0; i < cloumns.length; i++) {
                cellHeader = row.createCell(i);
                cellHeader.setCellValue(new XSSFRichTextString(cloumns[i]));
            }

5.遍历集合产生数据集,dataList就是从数据库查询出来的一个List集合

 for (int i = 0; i < dataList.size(); i++) {
                row = sheet.createRow(i+1);
                volRow=volSheet.createRow(i);
                temRow=temSheet.createRow(i);
                tmp = dataList.get(i);
                int index = 0;
 cell = row.createCell(index++);cell.setCellValue(new XSSFRichTextString(tmp.getName()));

6.写入excel

workbook.write(out);

7.关闭读写流

  out.close();
workbook.close();

完整代码:

 public static void  createExecl(String title,String[] cloumns,List<BatteryBean> dataList, String excelPath ){
        
        //1、判断文件是否存在，存在的话，就追加新的sheet页，不存在就新建
        FileOutputStream out = null;
        // 声明一个工作薄
        XSSFWorkbook workbook = null;
        try
        {
            out = new FileOutputStream(excelPath);
            workbook = new XSSFWorkbook();
            // 生成一个表格
            XSSFSheet sheet = workbook.createSheet(title);
            XSSFSheet volSheet=workbook.createSheet("title1");
            XSSFSheet temSheet=workbook.createSheet("title2");
            //2、生成第一行，作为头部
            XSSFRow row = sheet.createRow(0);
         
            XSSFRow volRow = volSheet.createRow(0);
         
            XSSFRow temRow= temSheet.createRow(0);
            XSSFCell cellHeader;
            for (int i = 0; i < cloumns.length; i++) {
                cellHeader = row.createCell(i);
                cellHeader.setCellValue(new XSSFRichTextString(cloumns[i]));
            }
            //3、遍历集合数据，产生数据行
            BatteryBean tmp;
            XSSFCell cell;
            
            for (int i = 0; i < dataList.size(); i++) {
                row = sheet.createRow(i+1);
                volRow=volSheet.createRow(i);
                temRow=temSheet.createRow(i);
                tmp = dataList.get(i);
                int index = 0;
                cell = row.createCell(index++);cell.setCellValue(new XSSFRichTextString(tmp.getName()));

               
                }
            }
            workbook.write(out);
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
            	
        }finally {
            try
            {
                out.close();
                workbook.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
                	
            }
        }
        
    }

下载出来的数据

有问题可以私信博主