POI操作Excel的简单操作

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();//设置第几列的宽度，宽度最大值：65280sheet.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 */@Testpublic 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);//设置文字旋转的 °Cstyle.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包：