java操作Excel表格(2007以上)

第一步:添加依赖:

  org.apache.poi
  poi-ooxml
  3.15

读excel

public void doBusiness() {
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(formPath));
        XSSFSheet sheet = workbook.getSheetAt(0);//获取第一个工作表对象
        int rows = sheet.getPhysicalNumberOfRows();// 获取表行数
        
        //XSSFRow xssfRow = sheet.getRow(0);//得到一行，进而下一步得到列数
        //int rsColumns = xssfRow.getPhysicalNumberOfCells();// 获取列数
        
        for (int i = 1; i < rows; i++) {//这里i从1取值是因为表格的第一行为表头,表头一般不需要处理.
            
            XSSFCell cell = sheet.getRow(i).getCell(1);// 获取第i行数据的第1列(注意:此处行与列计数都是从0开始的)
            double age = cell.getNumericCellValue();//列值为数字
            String name=cell.getStringCellValue();//列值为文本
            Date dealDate=cell.getDateCellValue();//列值为日期
            //........后续处理.......
        }
    } catch (Exception e) {
        log.error("出现异常,异常信息:{}",e);
    }
}

写excel

package xls;
 
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 

public class writeExcel {
 
	/**
	 * 写入
	 * @param path
	 */
	public void writeExcel2007(String path){
		XSSFWorkbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Fonts");
       
        Font font0 = wb.createFont();
        font0.setColor(IndexedColors.BROWN.getIndex());
        CellStyle style0 = wb.createCellStyle();
        style0.setFont(font0);
 
        Font font1 = wb.createFont();
        font1.setFontHeightInPoints((short)14);
        font1.setFontName("Courier New");
        font1.setColor(IndexedColors.RED.getIndex());
        CellStyle style1 = wb.createCellStyle();
        style1.setFont(font1);
 
        Font font2 = wb.createFont();
        font2.setFontHeightInPoints((short)16);
        font2.setFontName("Arial");
        font2.setColor(IndexedColors.GREEN.getIndex());
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
 
        Font font3 = wb.createFont();
        font3.setFontHeightInPoints((short)18);
        font3.setFontName("Times New Roman");
        font3.setColor(IndexedColors.LAVENDER.getIndex());
        CellStyle style3 = wb.createCellStyle();
        style3.setFont(font3);
 
        Font font4 = wb.createFont();
        font4.setFontHeightInPoints((short)18);
        font4.setFontName("Wingdings");
        font4.setColor(IndexedColors.GOLD.getIndex());
        CellStyle style4 = wb.createCellStyle();
        style4.setFont(font4);
 
        Font font5 = wb.createFont();
        font5.setFontName("Symbol");
        CellStyle style5 = wb.createCellStyle();
        style5.setFont(font5);
 
        XSSFCell cell0 = sheet.createRow(0).createCell(1);
        cell0.setCellValue("Default");
        cell0.setCellStyle(style0);
 
        XSSFCell cell1 = sheet.createRow(1).createCell(1);
        cell1.setCellValue("Courier");
        cell1.setCellStyle(style1);
 
        XSSFCell cell2 = sheet.createRow(2).createCell(1);
        cell2.setCellValue("Arial中文内容");
        cell2.setCellStyle(style2);
 
        XSSFCell cell3 = sheet.createRow(3).createCell(1);
        cell3.setCellValue("Times New Roman");
        cell3.setCellStyle(style3);
 
        XSSFCell cell4 = sheet.createRow(4).createCell(1);
        cell4.setCellValue("Wingdings");
        cell4.setCellStyle(style4);
 
        XSSFCell cell5 = sheet.createRow(5).createCell(1);
        cell5.setCellValue("Symbol");
        cell5.setCellStyle(style5);
 
        // Write the output to a file
        FileOutputStream fileOut;
		try {
			fileOut = new FileOutputStream(path);
			wb.write(fileOut);
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		writeExcel test = new writeExcel();
		test.writeExcel2007("D:\\2007.xlsx");
	}
}