POI 操作XLSX文件案例

最新推荐文章于 2022-01-20 13:25:42 发布

qq_29461579

最新推荐文章于 2022-01-20 13:25:42 发布

阅读量1.2k

点赞数

分类专栏： POI 文章标签： POI

本文链接：https://blog.csdn.net/qq_29461579/article/details/82026616

版权

本文详细介绍了如何使用Apache POI库来操作XLSX文件，包括读取、写入和修改Excel数据。通过实例代码，读者将掌握处理Excel文件的核心技巧。

摘要由CSDN通过智能技术生成

package test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
 * @author jack hao
 * 2018年8月24日
 * TODOxlsx 文件创建测试
 */
public class TestExcelWhitOutGirdlines {
	public static void main(String[] args) {
		new TestExcelWhitOutGirdlines().createBody();
	}
/**
 * 
 * TODO创建xlsx 文件的样式及数据
 * @author jack hao
 * @void
 */
	public void createBody() {
		XSSFWorkbook xwb = new XSSFWorkbook();
		XSSFSheet sheet = xwb.createSheet("test");

		// turn off gridlines 设置网格线 打印
		sheet.setDisplayGridlines(false);
		sheet.setPrintGridlines(false);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);

		// 设置 下面五列的宽度
		sheet.setColumnWidth(1, 256 * 26);
		sheet.setColumnWidth(2, 256 * 50);
		sheet.setColumnWidth(3, 256 * 12);
		sheet.setColumnWidth(4, 256 * 20);
		sheet.setColumnWidth(5, 256 * 16);
		
		int lrNum = sheet.getLastRowNum();
		if(lrNum !=0){
			lrNum =lrNum+2;
		}

		// 创建 第一行
		XSSFRow row0 = sheet.createRow(lrNum);
		XSSFCell cell0_1 = row0.createCell(1);
		cell0_1.setCellValue("#######");

		XSSFCell cell0_5 = row0.createCell(5);
		cell0_5.setCellValue("#######");
		
		//创建第二行 
		XSSFRow row2 = sheet.createRow(lrNum+1);
		CellRangeAddress region2_1_5 = new CellRangeAddress(lrNum+1, lrNum+1, 1, 5);
	    sheet.addMergedRegion(region2_1_5);
		XSSFCell cell2_1 = row2.createCell(1);
		cell2_1.setCellStyle(getMyCellStyle(cell2_1, xwb));
		setFontCenter(cell2_1);
		cell2_1.setCellValue("################ ");
		
		//创建第四行
		XSSFRow row4 = sheet.createRow(3);
		row4.setHeightInPoints(28.0f);
		XSSFCell cell4_1 = row4.createCell(1);
		cell4_1.setCellStyle(getMyCellStyleLeftAndTopStrong(cell4_1, xwb));
		cell4_1.setCellValue("#######");
		
		XSSFCell cell4_2 = row4.createCell(2);
		cell4_2.setCellStyle(getMyCellStyleTopStrong(cell4_2, xwb));
		
		XSSFCell cell4_3 = row4.createCell(3);
		cell4_3.setCellStyle(getMyCellStyleTopStrong(cell4_3, xwb));
		cell4_3.setCellValue("#######");
		
		CellRangeAddress region4_4_5 = new CellRangeAddress(3, 3, 4, 5);
		sheet.addMergedRegion(region4_4_5);
		
		//创建第五行
		XSSFRow row5 = sheet.createRow(4);
		row5.setHeightInPoints(32.0f);
		XSSFCell cell5_1 = row5.createCell(1);
		cell5_1.setCellStyle(getMyCellStyleLeftStrong(cell5_1, xwb));
		cell5_1.setCellValue("#######");
		
		//合并单元格
		CellRangeAddress region5_7_2_2 = new CellRangeAddress(4, 6, 2, 2);
		sheet.addMergedRegion(region5_7_2_2);
		
		//合并单元格
		CellRangeAddress region5_9_3_4 = new CellRangeAddress(4,8,3,4);
		sheet.addMergedRegion(region5_9_3_4);
		
		XSSFCel