利用POI技术实现导出excel报表
程序员文章站
2024-03-20 22:53:40
...
这个是很固定的代码步骤,下面会有三段代码,前面两段是基础介绍,最后一段基本可以复制下来,所有场景基本上都能套用。
maven工程中所需要的poi依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
一、向excel表格中写数据
@Test
public void TestPoi() throws Exception {
//获取对应的excel文件,工作簿文件
XSSFWorkbook sheets = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = sheets.createSheet();
sheets.createSheet("nishi");
//创建工作表中的行对象,方法中的数字代表行的索引,第一行的索引是0
XSSFRow row = sheet.createRow(1);
//创建工作表中的列对象,方法中的数字代表行的索引,第一列的索引是0
XSSFCell cell = row.createCell(1);
//在列中写数据
cell.setCellValue("你好");
//创建一个文件对象,作为excel文件内容的输出文件
File file = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
FileOutputStream fileOutputStream = new FileOutputStream(file);
//将内存中的workboook数据写入流中
sheets.write(fileOutputStream);
sheets.close();
fileOutputStream.close();
}
二、在excel表格中读取数据
@Test
public void TestReadPoi() throws Exception {
//获取要读取文件的工作簿对象
XSSFWorkbook wb = new XSSFWorkbook("test.xlsx");
/*//获取工作表,根据表格的名称拿数据
XSSFSheet sheet = wb.getSheet("sheet0");*/
//获取工作表,根据表格的索引拿数据
XSSFSheet sheet = wb.getSheetAt(0);
//获取行
XSSFRow row = sheet.getRow(1);
//获取列
XSSFCell cell = row.getCell(1);
//根据数据类型获取数据
String data = cell.getStringCellValue();
System.out.println(data);
//释放资源
wb.close();
}
三、循环往excel中写入数据
@Test
public void testProjectPoi() throws IOException {
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet s = wb.createSheet("题目数据文件");
//设置通用配置
// s.setColumnWidth(4,100);
CellStyle cs_field = wb.createCellStyle();
cs_field.setAlignment(HorizontalAlignment.CENTER);
cs_field.setBorderTop(BorderStyle.THIN);
cs_field.setBorderBottom(BorderStyle.THIN);
cs_field.setBorderLeft(BorderStyle.THIN);
cs_field.setBorderRight(BorderStyle.THIN);
//制作标题
s.addMergedRegion(new CellRangeAddress(1,1,1,12));
Row row_1 = s.createRow(1);
Cell cell_1_1 = row_1.createCell(1);
cell_1_1.setCellValue("在线试题导出信息");
//创建一个样式
CellStyle cs_title = wb.createCellStyle();
cs_title.setAlignment(HorizontalAlignment.CENTER);
cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_1_1.setCellStyle(cs_title);
//制作表头
String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述",
"题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
//创建第二行的行对象
Row row_2 = s.createRow(2);
//遍历写表头
for (int i = 0; i < fields.length; i++) {
Cell cell_2_temp = row_2.createCell(1 + i); //++
cell_2_temp.setCellValue(fields[i]); //++
cell_2_temp.setCellStyle(cs_field);
}
//制作数据区,下面是为了测试随便写的数据,一般是通过数据库来拿数据进行展示,然后写出到excel中
List<Question> questionList = new ArrayList<>();
Question qq = new Question();
qq.setId("1");
qq.setPicture("12");
qq.setReviewStatus("13");
qq.setAnalysis("14");
qq.setCatalogId("15");
qq.setCompanyId("16");
qq.setDifficulty("17");
qq.setIsClassic("18");
qq.setRemark("19");
qq.setState("21");
qq.setSubject("31");
qq.setType("41");
questionList.add(qq);
//定义行自增的初始值
int row_index = 0;
//遍历写数据
for (Question q : questionList) {
//定义列的初始自增值
int cell_index = 0;
//创建第三行的行对象,每写完一次,行数加1,写下一行
Row row_temp = s.createRow(3 + row_index++);
Cell cell_data_1 = row_temp.createCell(1 + cell_index++);
cell_data_1.setCellValue(q.getId()); //++
cell_data_1.setCellStyle(cs_field);
Cell cell_data_2 = row_temp.createCell(1 + cell_index++);
cell_data_2.setCellValue(q.getCompanyId()); //++
cell_data_2.setCellStyle(cs_field);
Cell cell_data_3 = row_temp.createCell(1 + cell_index++);
cell_data_3.setCellValue(q.getCatalogId()); //++
cell_data_3.setCellStyle(cs_field);
Cell cell_data_4 = row_temp.createCell(1 + cell_index++);
cell_data_4.setCellValue(q.getRemark()); //++
cell_data_4.setCellStyle(cs_field);
Cell cell_data_5 = row_temp.createCell(1 + cell_index++);
cell_data_5.setCellValue(q.getSubject()); //++
cell_data_5.setCellStyle(cs_field);
Cell cell_data_6 = row_temp.createCell(1 + cell_index++);
cell_data_6.setCellValue(q.getPicture()); //++
cell_data_6.setCellStyle(cs_field);
Cell cell_data_7 = row_temp.createCell(1 + cell_index++);
cell_data_7.setCellValue(q.getAnalysis()); //++
cell_data_7.setCellStyle(cs_field);
Cell cell_data_8 = row_temp.createCell(1 + cell_index++);
cell_data_8.setCellValue(q.getType()); //++
cell_data_8.setCellStyle(cs_field);
Cell cell_data_9 = row_temp.createCell(1 + cell_index++);
cell_data_9.setCellValue(q.getDifficulty()); //++
cell_data_9.setCellStyle(cs_field);
Cell cell_data_10 = row_temp.createCell(1 + cell_index++);
cell_data_10.setCellValue(q.getIsClassic()); //++
cell_data_10.setCellStyle(cs_field);
Cell cell_data_11 = row_temp.createCell(1 + cell_index++);
cell_data_11.setCellValue(q.getState()); //++
cell_data_11.setCellStyle(cs_field);
Cell cell_data_12 = row_temp.createCell(1 + cell_index++);
cell_data_12.setCellValue(q.getReviewStatus()); //++
cell_data_12.setCellStyle(cs_field);
}
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
上一篇: Oracle 数组使用整理
下一篇: iOS cocoapods安装和使用