POI操作excel

最新推荐文章于 2023-12-28 23:21:46 发布

yiwanxinyuefml

最新推荐文章于 2023-12-28 23:21:46 发布

阅读量529

点赞数

分类专栏： java基础文章标签： poi excel操作

本文链接：https://blog.youkuaiyun.com/yiwanxinyuefml/article/details/22811225

版权

java基础专栏收录该内容

19 篇文章

订阅专栏

最近要做一个项目文档管理的东西，需要用到excel进行读写操作，找了一些资料记录一下方便以后查询。

过程中碰到几个问题，

1）貌似生成xlsx用XSSFWorkbook，生成xls用HSSFWorkbook，不然打开生成的excel文件提示错误，暂时没去研究咋回事。。。

2）合并单元格，用下面这个方法

//合并单元格
//参数：起始行、结束行、起始列、结束列

sheet.addMergedRegion(new CellRangeAddress(1,2, 0, 0));

//取消合并单元格

//rowIndex 从第几行开始



public static void unMergedAll(Sheet sheet,int rowIndex) { 
		try {
			int lastRowNum = sheet.getLastRowNum();
			for(int i = rowIndex; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				for(int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
					unMergedCell(sheet,i,j);
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 取消合并单元格 
	 * @param sheet
	 * @param rowIndex
	 * @param colIndex
	 */
	public static void unMergedCell(Sheet sheet, int rowIndex, int colIndex) {
	    int num = sheet.getNumMergedRegions();
	    for(int i = 0; i < num; i++) {
	        CellRangeAddress range = sheet.getMergedRegion(i);
	        if(range.getFirstColumn() == colIndex && range.getFirstRow() == rowIndex){
	           sheet.removeMergedRegion(i) ;
	           break;
	        }
	    }
	}









参考资料：




一、apache poi 官方地址：http://poi.apache.org/apidocs  里面有一些类、接口、方法的介绍，具体可以参考。
二、 POI使用（http://blog.sina.com.cn/s/blog_6793ffde0101kzxm.html）
1）创建工作簿

//创建一个工作薄   
XSSFWorkbook wb = new XSSFWorkbook();   
//创建一个电子表格createSheet   
XSSFSheet sheet = wb.createSheet("创建一个带名字的电子表格");   
//XSSFSheet sheet = wb.createSheet();//调用默认构造创建电子表格   
//创建第三行,行和列都是从0开始计算的   
XSSFRow row = sheet.createRow((short) 2);   
row.setHeightInPoints(30);//设置行高30   
//1-8行的列宽为256像素 15在这里表示一个像素   
for (int i = 0; i < 8; i++) {   
    //column width is set in units of 1/256th of a character width   
    sheet.setColumnWidth(i, 256 * 15);   
}   
2）输出到文件或流


FileOutputStream fileOut = new FileOutputStream("E:/temp/study/xssf-align.xlsx");   
wb.write(fileOut);   
fileOut.close();  3）单元格剧中设置


createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);   
createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);   
createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);   
createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);   
createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);   
createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);   
createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);   
   
ate static void createCell(XSSFWorkbook wb, XSSFRow row, short column,   
    short halign, short valign) {   
XSSFCell cell = row.createCell(column);   
cell.setCellValue(new XSSFRichTextString("Align It"));   
//=======创建单元格样式:开始===========   
CellStyle cellStyle = wb.createCellStyle();   
cellStyle.setAlignment(halign);   
cellStyle.setVerticalAlignment(valign);   
//=======创建单元格样式:结束===========   
cell.setCellStyle(cellStyle);   
4）使用样式库


private static void generate(Writer out, Map styles) throws Exception {   
   
     Random rnd = new Random();   
     Calendar calendar = Calendar.getInstance();   
   
     SpreadsheetWriter sw = new SpreadsheetWriter(out);   
     sw.beginSheet();   
   
     //创建头部行   
     sw.insertRow(0);   
     int styleIndex = styles.get("header").getIndex();   
     sw.createCell(0, "Title", styleIndex);   
     sw.createCell(1, "% Change", styleIndex);   
     sw.createCell(2, "Ratio", styleIndex);   
     sw.createCell(3, "Expenses", styleIndex);   
     sw.createCell(4, "Date", styleIndex);   
   
     sw.endRow();   
   
     //插入数据行   
     for (int rownum = 1; rownum < 100000; rownum++) {   
         sw.insertRow(rownum);   
   
         sw.createCell(0, "Hello, " + rownum + "!");   
         sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());   
         sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());   
         sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());   
         sw.createCell(4, calendar, styles.get("date").getIndex());   
   
         sw.endRow();   
   
         calendar.roll(Calendar.DAY_OF_YEAR, 1);   
     }   
     sw.endSheet();   
 }   


6）创建各种类型单元格


// 创建数字类型的单元格   
Cell cell = row.createCell((short)0);   
cell.setCellValue(1);   
row.createCell(1).setCellValue(1.2);   
//创建单元格并接设置值为简单字符串   
row.createCell(2).setCellValue("This is a string cell");   
//创建单元格并接设置值为富文本   
RichTextString str = creationHelper.createRichTextString("Apache");   
Font font = wb.createFont();   
font.setItalic(true);   
font.setUnderline(Font.U_SINGLE);   
str.applyFont(font);   
row.createCell(3).setCellValue(str);   
   
//创建boolean类型的单元格   
row.createCell(4).setCellValue(true);   
//创建单元格，当前单元的值是通过公式得到的 formula   
row.createCell(5).setCellFormula("SUM(A1:B1)");   
//创建日期类型的单元格并接进行格式化   
CellStyle style = wb.createCellStyle();   
style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));   
cell = row.createCell(6);   
cell.setCellValue(new Date());   
cell.setCellStyle(style);   
//创建超级类型的单元格   
row.createCell(7).setCellFormula("SUM(A1:B1)");                    cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")"); 7）自定义单元格数据格式


Workbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();   
Sheet sheet = wb.createSheet("format sheet");   
DataFormat format = wb.createDataFormat();   
Row row = sheet.createRow(0);   
Cell cell = row.createCell(0);   
cell.setCellValue(11111.25);   
//创建单元格样式   
CellStyle style = wb.createCellStyle();      
style.setDataFormat(format.getFormat("0.0"));   
cell.setCellStyle(style);   
   
row = sheet.createRow(1);   
cell = row.createCell(0);   
cell.setCellValue(11111.25);   
style = wb.createCellStyle();   
style.setDataFormat(format.getFormat("#,##0.0000"));   
cell.setCellStyle(style);   


8）单元格上添加注释


CreationHelper factory = wb.getCreationHelper();   
   
Sheet sheet = wb.createSheet();   
//创建单元格1   
Cell cell1 = sheet.createRow(3).createCell(5);   
cell1.setCellValue("F4");   
//单元格1添加注释   
Drawing drawing = sheet.createDrawingPatriarch();   
ClientAnchor anchor = factory.createClientAnchor();   
Comment comment1 = drawing.createCellComment(anchor);   
RichTextString str1 = factory.createRichTextString("Hello, World!");   
comment1.setString(str1);   
comment1.setAuthor("Apache POI");   
cell1.setCellComment(comment1);   
//创建单元格2   
Cell cell2 = sheet.createRow(2).createCell(2);   
cell2.setCellValue("C3");   
//给单元格2添加注释   
Comment comment2 = drawing.createCellComment(anchor);   
RichTextString str2 = factory.createRichTextString("XSSF can set cell comments");   
//设置注释字体的样式   
Font font = wb.createFont();   
font.setFontName("Arial");   
font.setFontHeightInPoints((short)14);   
font.setBoldweight(Font.BOLDWEIGHT_BOLD);   
font.setColor(IndexedColors.RED.getIndex());   
str2.applyFont(font);   
   
comment2.setString(str2);   
comment2.setAuthor("Apache POI");   
comment2.setColumn(2);   
comment2.setRow(2);


9）单元格背景颜色设置


// Aqua background   
//创建样式   
CellStyle style = wb.createCellStyle();   
//设值添加背景色   
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());   
style.setFillPattern(CellStyle.BIG_SPOTS);   
   
Cell cell = row.createCell((short) 1);   
cell.setCellValue(new XSSFRichTextString("X"));   
cell.setCellStyle(style);   
   
// Orange "foreground", foreground being the fill foreground not the font color.   
//创建样式   
style = wb.createCellStyle();   
//设值添加背景色 style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());   
style.setFillPattern(CellStyle.SOLID_FOREGROUND);   
cell = row.createCell((short) 2);   
cell.setCellValue(new XSSFRichTextString("X"));   
cell.setCellStyle(style);   


10）单元格设置链接、调用邮件客户端


CreationHelper createHelper = wb.getCreationHelper();   
//超级链接的样式,蓝色并接默认有下划线   
CellStyle hlink_style = wb.createCellStyle();   
Font hlink_font = wb.createFont();   
hlink_font.setUnderline(Font.U_SINGLE);   
hlink_font.setColor(IndexedColors.BLUE.getIndex());   
hlink_style.setFont(hlink_font);   
//创建链接到http://poi.apache.org/的超级链接   
Cell cell;   
Sheet sheet = wb.createSheet("Hyperlinks");   
//URL   
cell = sheet.createRow(0).createCell((short)0);   
cell.setCellValue("URL Link");   
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);   
link.setAddress("http://poi.apache.org/");   
cell.setHyperlink(link);   
cell.setCellStyle(hlink_style);   
   
//链接指定目录的文件   
cell = sheet.createRow(1).createCell((short)0);   
cell.setCellValue("File Link");   
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);   
link.setAddress("c://link1.xls");   
cell.setHyperlink(link);   
cell.setCellStyle(hlink_style);   
   
//调用本地客户端发送邮件   
cell = sheet.createRow(2).createCell((short)0);   
cell.setCellValue("Email Link");   
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);   
//note, if subject contains white spaces, make sure they are url-encoded   
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");   
cell.setHyperlink(link);   
cell.setCellStyle(hlink_style);   
   
//link to a place in this workbook   
   
//创建一个定位到Target Sheet'!A1的链接   
Sheet sheet2 = wb.createSheet("Target Sheet");   
sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");   
   
cell = sheet.createRow(3).createCell((short)0);   
cell.setCellValue("Worksheet Link");   
Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);   
link2.setAddress("'Target Sheet'!A1");//连接到Target Sheet a1单元格   
cell.setHyperlink(link2);   
cell.setCellStyle(hlink_style);   


三、POI常见操作（http://m.blog.youkuaiyun.com/blog/zx1030/8069812）