POI操作

来源：互联网发布：js date 格式化输出编辑：程序博客网时间：2024/06/06 07:04

合并单元格，在HSSF中合并单元格是如下的操作    HSSFWorkbook wb = new HSSFWorkbook();    HSSFSheet sheet = wb.createSheet("new sheet");     HSSFRow row = sheet.createRow((short) 1);    HSSFCell cell = row.createCell((short) 1);    cell.setCellValue("This is a test of merging");     sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));//指定合并区域     // Write the output to a file    FileOutputStream fileOut = new FileOutputStream("workbook.xls");    wb.write(fileOut);    fileOut.close();

读excel判断不存在集合

 <pre name="code" class="java">package com.test;import java.io.File;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.sun.xml.internal.bind.v2.runtime.unmarshaller.XsiNilLoader.Array;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;public class EventExample {private staticWorkbook xls = null;public static Map<String,String> read(String filePath, int cell,int start,int end) throws IOException {Map<String,String> map=new HashMap<>();FileInputStream fileInputStream = new FileInputStream(new File(filePath));if (filePath.endsWith("xlsx")) {// 2007xls = new XSSFWorkbook(fileInputStream);} else if (filePath.endsWith("xls")) {// 2003xls = new HSSFWorkbook(fileInputStream);}  Sheet sheet = xls.getSheetAt(0);    for(int i=start;i<(end+1);i++){ String result=sheet.getRow(i).getCell(cell).getStringCellValue(); map.put(result,result); } return map; }public static void main(String args[]) throws Exception {/* * 知识点 * Map<String, String> map=read("E:/java/工作/标准/知识点标准结构-高中.xls",1,2,56); Map<String, String> map2=read("E:/java/工作/标准/田中双向细目表.xls",3,2,362);*/ Map<String, String> map=read("E:/java/工作/标准/能力值标准结构-高中.xls",1,2,39); Map<String, String> map2=read("E:/java/工作/标准/田中双向细目表.xls",4,2,362); List<String> list=new ArrayList<>(); list.addAll(map2.values());     int i=0; for(String s:list){ if(null==map.get(s) ){  System.out.println( s);i++;}  } System.out.println(i);  }public static void setCol(String filePath,int rows) throws IOException{CellStyle s=xls.createCellStyle();     s.setFillBackgroundColor(HSSFColor.BLUE.index);              Map<String,String> map=new HashMap<>();FileInputStream fileInputStream = new FileInputStream(new File(filePath));if (filePath.endsWith("xlsx")) {// 2007xls = new XSSFWorkbook(fileInputStream);} else if (filePath.endsWith("xls")) {// 2003xls = new HSSFWorkbook(fileInputStream);}  Sheet sheet = xls.getSheetAt(0);    sheet.getRow(rows).setRowStyle(s);}}

注意

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

0 0