常用共能 之 poi
spring boot引入poi 只要引入 poi-ooxml jar即可
idea里打开pom文件 alt+insert就可添加依赖了
1、获取的单元格 cell可能为null ,说明前台单元格是空的或者其它 ,这时候就无法给单元设置背景色
要sheet.getRow(i).createCell(18).setCellStyle(cellStyle); 那种正常有内容的单元格直接sheet.getRow(i).getCell(18).setCellStyle(cellStyle);
2、获取到cell以后 判断cell的值类型
public static Object getValueByType(XSSFCell cell) throws NullPointerException, Exception{
Object value="";
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if(HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}else{
value = cell.getNumericCellValue();
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
value=cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
value="";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
value="";
break;
default:
System.out.print("未知类型 ");
break;
}
}
return value;
}
3、设置背景色
wb = new XSSFWorkbook(file.getInputStream());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充单元格
cellStyle.setFillForegroundColor(HSSFColor.RED.index); //填红色
CellStyle yellowCellStyle = wb.createCellStyle();
yellowCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充单元格
yellowCellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); //填红色
4、常用工具类
package com.yang.exceloperate;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelOperate {
public static void main(String[] args) {
// 创建Excel表格
createExcel(getStudent());
// 读取Excel表格
List<Student> list = readExcel();
System.out.println(list.toString());
}
/**
* 初始化数据
*
* @return 数据
*/
private static List<Student> getStudent() {
List<Student> list = new ArrayList<Student>();
Student student1 = new Student("小明", 8, "二年级");
Student student2 = new Student("小光", 9, "三年级");
Student student3 = new Student("小花", 10, "四年级");
list.add(student1);
list.add(student2);
list.add(student3);
return list;
}
/**
* 创建Excel
*
* @param list
* 数据
*/
private static void createExcel(List<Student> list) {
// 创建一个Excel文件
//HSSFworkbook 2003版excel XSSFWorkbook是操作Excel2007的版本excel 这两个都已读取excel或者写入excel
//从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的API----SXSSF 只能写入excel 不能从excel,适合大量数据从库中导入excel用
//当数据量超出65536条后,在使用HSSFWorkbook或XSSFWorkbook,程序会报OutOfMemoryError:Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
//三者使用的时候只要 new HSSFWorkbook(); 改变就行了,下面的api都一样
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表
HSSFSheet sheet = workbook.createSheet("学生表一");
// 添加表头行
HSSFRow hssfRow = sheet.createRow(0);
// 设置单元格格式居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 添加表头内容
HSSFCell headCell = hssfRow.createCell(0);
headCell.setCellValue("姓名");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(1);
headCell.setCellValue("年龄");
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(2);
headCell.setCellValue("年级");
headCell.setCellStyle(cellStyle);
// 添加数据内容
for (int i = 0; i < list.size(); i++) {
hssfRow = sheet.createRow((int) i + 1);
Student student = list.get(i);
// 创建单元格,并设置值
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(student.getName());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(1);
cell.setCellValue(student.getAge());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(2);
cell.setCellValue(student.getGrade());
cell.setCellStyle(cellStyle);
}
// 保存下载Excel文件
try {
OutputStream outputStream = new FileOutputStream("D:/students.xls");
workbook.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel
*
* @return 数据集合
*/
private static List<Student> readExcel() {
List<Student> list = new ArrayList<Student>();
HSSFWorkbook workbook = null;
try {
// 读取Excel文件
//ServletContext能够获得整个项目之内的文件
//InputStream inputStream=request.getServletContext().getResourceAsStream("/a.xls");
InputStream inputStream = new FileInputStream("D:/students.xls");
workbook = new HSSFWorkbook(inputStream);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
// 循环工作表 遍历每个表
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行 遍历行
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 将单元格中的内容存入集合
Student student = new Student();
HSSFCell cell = hssfRow.getCell(0);//得到第一列的数据
if (cell == null) {
continue;
}
student.setName(cell.getStringCellValue());
cell = hssfRow.getCell(1);//得到第二列的数据
if (cell == null) {
continue;
}
student.setAge((int) cell.getNumericCellValue());
cell = hssfRow.getCell(2);//得到第三列的数据
if (cell == null) {
continue;
}
student.setGrade(cell.getStringCellValue());
list.add(student);
}
}
return list;
}
}