POI操作Excel的一些常用对象、方法
package com.zsd.tool;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
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;
import org.junit.Test;
/**
* POI 操作Excel
* @author admini
* POI 的一些简介
POIFS (较差混淆技术实现文件系统) : 此组件是所有其他POI元件的基本因素。它被用来明确地读取不同的文件。
HSSF (电子表格格式) : 它被用来读取和写入MS-Excel文件的xls格式。
XSSF (XML格式) : 它是用于MS-Excel中xlsx文件格式。
HPSF (属性设置格式) : 它用来提取MS-Office文件属性设置。
HWPF (字处理器格式) : 它是用来读取和写入MS-Word的文档doc扩展名的文件。
XWPF (XML字处理器格式) : 它是用来读取和写入MS-Word的docx扩展名的文件。
HSLF (幻灯片版式格式) : 它是用于读取，创建和编辑PowerPoint演示文稿。
HDGF (图表格式) : 它包含类和方法为MS-Visio的二进制文件。
HPBF (出版商格式) : 它被用来读取和写入MS-Publisher文件。
*/
public class ExcelOperationPOI {
/**创建工作簿
* @throws IOException
*/
public void createWorkbook() throws IOException {
//创建一个空白工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
//创建文件输出流
FileOutputStream out = new FileOutputStream(new File("f:/poi.xlsx"));
//把空白工作薄写入文件流
workbook.write(out);
out.close();
System.out.println("创建成功！");
}
/**读取工作薄
* @throws Exception
*/
public void readWorkbook() throws Exception {
File file = new File("f:/poi.xlsx");
FileInputStream input = new FileInputStream(file);
//根据现有文件得到一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook(input);
XSSFWorkbook workbook1 = new XSSFWorkbook(file);
if (file.isFile() && file.exists()) {
System.out.println("成功");
} else {
System.out.println("失败，该文件不存在");
}
}
/**写入数据到工作薄
* @throws Exception
*/
public void writeDataWorkbook() throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个Sheet 表格
XSSFSheet sheet = workbook.createSheet("POI Sheet 1");
//创建 行
XSSFRow row;
//测试数据
Map<String, Object[]> empinfo = new TreeMap<String, Object[]>();
empinfo.put("1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
empinfo.put("2", new Object[] { "tp01", "Gopal", "Technical Manager" });
empinfo.put("3", new Object[] { "tp02", "Manisha", "Proof Reader" });
empinfo.put("4", new Object[] { "tp03", "Masthan", "Technical Writer" });
empinfo.put("5", new Object[] { "tp04", "Satish", "Technical Writer" });
empinfo.put("6", new Object[] { "tp05", "Krishna", "Technical Writer" });
Set < String > keyid = empinfo.keySet();
int rowid = 0;
for (String key : keyid) {
//创建行
row = sheet.createRow(rowid++);
Object[] objects = empinfo.get(key);
int cellid = 0;
for (Object obj : objects) {
//创建单元格
XSSFCell cell = row.createCell(cellid++);
cell.setCellValue((String)obj);
}
}
//文件输出流
FileOutputStream out = new FileOutputStream(new File("f:/poi.xlsx"));
//工作薄写入
workbook.write(out);
out.close();
System.out.println("成功");
}
/**
* 从工作薄读取数据
* @throws Exception
*/
public void readDataWorkbook() throws Exception {
FileInputStream input = new FileInputStream(new File("f:/poi.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(input);
XSSFSheet sheet = workbook.getSheetAt(0);
//得到 Row 迭代器
Iterator<Row> iterator = sheet.iterator();
XSSFRow row;
while (iterator.hasNext()) {
row = (XSSFRow)iterator.next();
//得到 Cell 迭代器
Iterator<Cell> iteratorCell = row.iterator();
while (iteratorCell.hasNext()) {
Cell cell = iteratorCell.next();
/*
* POI 3.15版本以前用的是 cell.getCellType()
* 3.15以后用的是 CellType 枚举 cell.getCellTypeEnum()
*/
switch (cell.getCellTypeEnum()) {
case NUMERIC:
System.out.print(cell.getNumericCellValue() + " | " );
break;
case STRING:
System.out.print(cell.getStringCellValue() + " | " );
break;
case BOOLEAN:
System.out.println(cell.getBooleanCellValue() + " | " );
break;
case FORMULA:
System.out.println(cell.getCellFormula() + " | " );
break;
case ERROR:
System.out.println(cell.getErrorCellValue() + " | " );
break;
}
}
System.out.println();
}
input.close();
}
/**为单元格写入样式
* @throws Exception
*/
public void setCellStyle() throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("单元格样式");
XSSFRow row = sheet.createRow((short) 1);
//设置行的高度
row.setHeight((short) 800);
XSSFCell cell = (XSSFCell) row.createCell((short) 1);
cell.setCellValue("列合并");
// 合并单元格
sheet.addMergedRegion(
//表格的行，列 都是以 0 为下标开始
new CellRangeAddress(1, // 第几行开始
1, // 第几行结束
1, // 第几列开始
4 // 第几列结束
));
/**
* 单元格 的对齐方式
*/
/*
* 左上角对齐
*/
row = sheet.createRow(5);
cell = (XSSFCell) row.createCell(0);
row.setHeight((short) 1000);
//创建 XSSFCellStyle对象
XSSFCellStyle style1 = workbook.createCellStyle();
//设置第几列的宽度，宽度最大值：65280
sheet.setColumnWidth(0, 8000);
/*
* setAlignment(short) 传入这种参数类型的方法，自3.15版本以后被弃用
* 使用枚举的方式
*/
//对齐
style1.setAlignment(HorizontalAlignment.LEFT);
//垂直对齐
style1.setVerticalAlignment(VerticalAlignment.TOP);
//style1.setAlignment(XSSFCellStyle.ALIGN_LEFT);
//style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
cell.setCellValue("左上角对齐");
cell.setCellStyle(style1);
/*
* 中心对齐
*/
row = sheet.createRow(6);
cell = (XSSFCell) row.createCell(1);
row.setHeight((short) 800);
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
//style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cell.setCellValue("中心对齐");
cell.setCellStyle(style2);
/*
* 右下角对齐
*/
row = sheet.createRow(7);
cell = (XSSFCell) row.createCell(2);
row.setHeight((short) 800);
XSSFCellStyle style3 = workbook.createCellStyle();
style3.setAlignment(HorizontalAlignment.RIGHT);
style3.setVerticalAlignment(VerticalAlignment.BOTTOM);
//style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
//style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
cell.setCellValue("右下角对齐");
cell.setCellStyle(style3);
/*
* 左右对齐
*/
row = sheet.createRow(8);
cell = (XSSFCell) row.createCell(3);
XSSFCellStyle style4 = workbook.createCellStyle();
style4.setAlignment(HorizontalAlignment.JUSTIFY);
style4.setVerticalAlignment(VerticalAlignment.JUSTIFY);
//style4.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
//style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
cell.setCellValue("左右对齐左右对齐");
cell.setCellStyle(style4);
/*
* 单元格边框
*/
row = sheet.createRow((short) 10);
row.setHeight((short) 800);
cell = (XSSFCell) row.createCell((short) 1);
cell.setCellValue("单元格边框");
XSSFCellStyle style5 = workbook.createCellStyle();
//设置边框样式
style5.setBorderBottom(BorderStyle.THICK);
style5.setBorderLeft(BorderStyle.DOUBLE);
style5.setBorderRight(BorderStyle.HAIR);
style5.setBorderTop(BorderStyle.DASH_DOT);
//style5.setBorderBottom(XSSFCellStyle.BORDER_THICK);
//style5.setBorderLeft(XSSFCellStyle.BORDER_DOUBLE);
//style5.setBorderRight(XSSFCellStyle.BORDER_HAIR);
//style5.setBorderTop(XSSFCellStyle.BIG_SPOTS);
//设置边框的颜色
style5.setBottomBorderColor(IndexedColors.BLUE.getIndex());
style5.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style5.setRightBorderColor(IndexedColors.RED.getIndex());
style5.setTopBorderColor(IndexedColors.CORAL.getIndex());
cell.setCellStyle(style5);
/*
* 单元格的背景颜色
*/
row = sheet.createRow((short) 10);
cell = (XSSFCell) row.createCell((short) 1);
XSSFCellStyle style6 = workbook.createCellStyle();
style6.setFillBackgroundColor(HSSFColorPredefined.LEMON_CHIFFON.getIndex());
style6.setFillPattern(FillPatternType.LESS_DOTS);
style6.setAlignment(HorizontalAlignment.FILL);
/*style6.setFillBackgroundColor(HSSFColor.LEMON_CHIFFON.index);
style6.setFillPattern(XSSFCellStyle.LESS_DOTS);
style6.setAlignment(XSSFCellStyle.ALIGN_FILL);*/
sheet.setColumnWidth(1, 8000);
cell.setCellValue("单元格背景颜色");
cell.setCellStyle(style6);
/*
* 前景颜色
*/
row = sheet.createRow((short) 12);
cell = (XSSFCell) row.createCell((short) 1);
XSSFCellStyle style7 = workbook.createCellStyle();
style7.setFillForegroundColor(HSSFColorPredefined.BLUE.getIndex());
style7.setFillPattern(FillPatternType.LESS_DOTS);
style7.setAlignment(HorizontalAlignment.FILL);
/*style7.setFillForegroundColor(HSSFColor.BLUE.index);
style7.setFillPattern(XSSFCellStyle.LESS_DOTS);
style7.setAlignment(XSSFCellStyle.ALIGN_FILL);*/
cell.setCellValue("单元格前景颜色");
cell.setCellStyle(style7);
// 样式写入文件
FileOutputStream out = new FileOutputStream(new File("f:/cellstyle.xlsx"));
workbook.write(out);
out.close();
System.out.println("成功");
}
/**为单元格设置字体样式
* @throws Exception
*/
@Test
public void setFontStyle() throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Fontstyle");
XSSFRow row = sheet.createRow(2);
// 创建字体样式
XSSFFont font = workbook.createFont();
//字体大小
font.setFontHeightInPoints((short) 60);
//字体名称
font.setFontName("华文行楷");
//是否斜体
font.setItalic(true);
//是否粗体
font.setBold(true);
//字体颜色
font.setColor(HSSFColorPredefined.BRIGHT_GREEN.getIndex());
//font.setColor(HSSFColor.BRIGHT_GREEN.index);
// 设置字体样式
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
//设置文字旋转的 °C
style.setRotation((short) 100);
// 创建单元格
XSSFCell cell = row.createCell(1);
cell.setCellValue("字体样式");
cell.setCellStyle(style);
FileOutputStream out = new FileOutputStream(new File("f:/fontstyle.xlsx"));
workbook.write(out);
out.close();
System.out.println("成功");
}
}
JAR包下载：http://pan.baidu.com/s/1i5QflC5
用的JAR包：
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
