常用共能 之 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;
	}
}