java poi excel 操作类 支持2003，2007

原文转自: http://www.ij2ee.com/2011/05/05/java-poi-excel-%E6%93%8D%E4%BD%9C%E7%B1%BB-%E6%94%AF%E6%8C%812003%EF%BC%8C2007.html

package com.oow.util;



import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;



import org.apache.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFFooter;

import org.apache.poi.hssf.usermodel.HSSFHeader;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.contrib.CellUtil;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;



/**

 * @author Jeson

 * @blog:http://www.ij2ee.com

 * @date:2011-5-4 下午01:20:17

 * @version :1.0

 * 

 */

public class ExcelUtil2 {



    public static int maxRowSize = 60000;



    public static void main(String[] args) throws Exception {

String excelPath = "D:/test.xlsx";

// testReadExcel(excelPath);

// testCreateExcel(excelPath);

testData2Excel(excelPath);

// testReadFrom2007(excelPath);

    }



    private static void testData2Excel(String excelPath) {

List a = new ArrayList();

Object[] os = new Object[2];

os[0] = 1;

os[1] = 2;

a.add(os);

a.add(os);

a.add(os);

a.add(os);

a.add(os);

a.add(os);

Object[] tt = new Object[2];

tt[0] = "tt1";

tt[1] = "tt2";

data2Excel2007(a, tt, excelPath);



    }



    private static void testReadFrom2007(String filePath) throws Exception {

Map map = parseFromFile(filePath);

Object tts[] = map.keySet().toArray();

for (Object tt : tts) {

   System.out.println("title -> " + tt);

   Object o = map.get(tt);

   List list = (List) o;

   for (Object os : list) {

List os1 = (ArrayList) os;

for (Object o1 : os1) {

   System.out.print(o1);

}

System.out.println();

   }

}

    }



    private static void testCreateExcel(String excelPath) throws IOException {

createExcel(excelPath);

HSSFWorkbook book = new HSSFWorkbook();

// setBorder(book,1,1,1,1);



HSSFSheet sheet = createSheet(book, "工作空间1");

sheet.setDisplayGridlines(true);

setSheetHeadAndFoot(sheet);

for (int i = 0; i < 100; i++) {

   HSSFRow row = createRow(sheet, i, null);



   for (int j = 0; j < 2; j++) {

System.out.print(j + "" + i);



HSSFCell cell = row.createCell(j);

cell.setCellValue(i + "" + j);

   }

   System.out.println();

}



// cs.setb

writeWorkbook(book, excelPath);



    }



    /**

     * 将数据导出到excel

     * 

     * @param datas

     *            数据 list型的

     * @param titles

     *            将作为excel的title 可以为null

     * @param filePath

     *            文件路径

     * @return

     * @throws FileNotFoundException

     */

    public static boolean data2Excel(List<Object> datas, Object[] titles,

   String filePath) throws FileNotFoundException {

boolean is2003 = is2003(filePath);

if (is2003) {

   data2Excel2003(datas, titles, filePath);

} else {

   data2Excel2007(datas, titles, filePath);

}

return true;

    }



    private static void data2Excel2007(List<Object> datas, Object[] titles,

   String filePath) {

XSSFWorkbook book = new XSSFWorkbook();

int allDataSize = datas.size();

double pageNum_ = Integer.valueOf(allDataSize).doubleValue()

/ Integer.valueOf(maxRowSize).doubleValue();

double sheetSize = Math.ceil(pageNum_);

// setBorder(book, 100, 100, 100, 100);

for (int i = 0; i < sheetSize; i++) {

   XSSFSheet sheet = book.createSheet("Sheet" + i);

   for (int j = 0; j < datas.size(); j++) {

XSSFRow row = sheet.createRow(j);

Object data = datas.get(j);

Object os[] = (Object[]) data;

for (int k = 0; k < os.length; k++) {

   XSSFCell xcell = row.createCell(k);

   xcell.setCellValue(os[k] + "");

}

   }

}

writeWorkbook2007(book, filePath);

    }



    private static void data2Excel2003(List<Object> datas, Object[] titles,

   String filePath) {

HSSFWorkbook book = new HSSFWorkbook();

int allDataSize = datas.size();

double pageNum_ = Integer.valueOf(allDataSize).doubleValue()

/ Integer.valueOf(maxRowSize).doubleValue();

double sheetSize = Math.ceil(pageNum_);

// setBorder(book, 100, 100, 100, 100);

for (int i = 0; i < sheetSize; i++) {

   HSSFSheet sheet = createSheet(book, "Sheet" + i);

   sheet.setDisplayGridlines(true);

   if (titles != null) {

HSSFRow row = createRow(sheet, 0, null);

int ttSize = titles.length;

for (int j = 0; j < ttSize; j++) {

   CellUtil.createCell(row, j, titles[j] + "");



}

for (int l = 0; l < datas.size(); l++) {

   Object data = datas.get(l);

   Object[] os = (Object[]) data;

   HSSFRow rowl = createRow(sheet, l + 1, null);

   Object[] d = (Object[]) data;

   for (int j = 0; j < d.length; j++) {

CellUtil.createCell(rowl, j, d[j] + "");



   }

}

   } else {

for (int l = 0; l < datas.size(); l++) {

   Object data = datas.get(l);

   Object[] os = (Object[]) data;

   HSSFRow row = createRow(sheet, l, null);

   Object[] d = (Object[]) data;

   for (int j = 0; j < d.length; j++) {

HSSFCellUtil.createCell(row, j, d[j] + "");



   }

}

   }

   writeWorkbook(book, filePath);

}

    }



    private static boolean is2003(String filePath) throws FileNotFoundException {

File file = new File(filePath);

if (!file.exists() || !file.isFile()) {

   throw new FileNotFoundException();

}

int len = filePath.length();

String su = filePath.substring(len – 5, len);

if (su.indexOf("xlsx") != -1) {

   return false;

} else if (su.indexOf("xls") != -1) {

   return true;

}

return false;

    }



    public static Map parseFromFile(String filePath) throws Exception {

boolean is2003 = is2003(filePath);

File file = new File(filePath);

return is2003 ? extractTextFromXLS(new FileInputStream(file))

: extractTextFromXLS2007(file);

    }



    public static Map extractTextFromXLS(InputStream inputStream)

   throws IOException {

Map context = new HashMap();

HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);

HSSFSheet hssfSheet = null;

List list_1 = null;

List list_2 = null;

HSSFRow hssfRow = null;

HSSFCell hssfCell = null;

for (int i = 0; i < hssfWorkbook.getNumberOfSheets(); ++i) {

   hssfSheet = hssfWorkbook.getSheetAt(i);

   if (null == hssfSheet) {

break;

   }

   list_1 = new ArrayList();

   for (int j = 0; j <= hssfSheet.getLastRowNum(); ++j) {

hssfRow = hssfSheet.getRow(j);

if (null == hssfRow) {

   list_1.add(null);

} else {

   list_2 = new ArrayList();

   for (int k = 0; k < hssfRow.getLastCellNum(); ++k) {

hssfCell = hssfRow.getCell(k);

if (null == hssfCell) {

   list_2.add(null);

} else if (hssfCell.getCellType() == 0) {

   list_2.add(Double.valueOf(

   hssfCell.getNumericCellValue()).toString());

} else if (hssfCell.getCellType() == 4) {

   list_2.add(Boolean.valueOf(

   hssfCell.getBooleanCellValue()).toString());

} else {

   list_2.add(hssfCell.getStringCellValue());

}

   }

   list_1.add(list_2);

}

   }

   context.put(hssfSheet.getSheetName(), list_1);

}

return context;

    }



    public static Map extractTextFromXLS2007(File paramFile) throws Exception {

return extractTextFromXLS2007(paramFile.getPath());

    }



    public static Map extractTextFromXLS2007(String filePath) throws Exception {

Map context = new HashMap();

System.out.println(" === " + filePath + " === ");

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(filePath);

List list_1 = null;

List list_2 = null;

XSSFSheet xssfSheet = null;

for (int i = 0; i < xssfWorkbook.getNumberOfSheets(); ++i) {

   xssfSheet = xssfWorkbook.getSheetAt(i);

   if (xssfSheet == null) {

break;

   }

   list_1 = new ArrayList();

   for (int j = 0; j <= xssfSheet.getLastRowNum(); ++j) {

XSSFRow xssfRow = xssfSheet.getRow(j);

if (null == xssfRow) {

   list_1.add(null);

} else {

   list_2 = new ArrayList();

   for (int k = 0; k < xssfRow.getLastCellNum(); ++k) {

XSSFCell xssfCell = xssfRow.getCell(k);

if (null == xssfCell) {

   list_2.add(null);

} else if (xssfCell.getCellType() == 4) {

   list_2.add(Boolean.valueOf(

   xssfCell.getBooleanCellValue()).toString());

} else if (xssfCell.getCellType() == 0) {

   list_2.add(Double.valueOf(

   xssfCell.getNumericCellValue()).toString());

} else {

   list_2.add(xssfCell.getStringCellValue());

}

   }

   list_1.add(list_2);

}

   }

   System.out.println("xSheet is " + xssfSheet.getSheetName());

   context.put(xssfSheet.getSheetName(), list_1);

}

return context;

    }



    /**

     * 

     * 功能：将HSSFWorkbook写入Excel文件

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param absPath

     *            写入文件的相对路径

     * 

     * @param wbName

     *            文件名

     */

    public static void writeWorkbook(HSSFWorkbook wb, String fileName) {

FileOutputStream fos = null;

try {

   fos = new FileOutputStream(fileName);

   wb.write(fos);

} catch (FileNotFoundException e) {

   log.error(new StringBuffer("[").append(e.getMessage()).append("]")

   .append(e.getCause()));

} catch (IOException e) {

   log.error(new StringBuffer("[").append(e.getMessage()).append("]")

   .append(e.getCause()));

} finally {

   try {

if (fos != null) {

   fos.close();

}

   } catch (IOException e) {

log.error(new StringBuffer("[").append(e.getMessage()).append(

"]").append(e.getCause()));

   }

}

    }



    /**

     * 

     * 功能：将XSSFWorkbook写入Excel文件

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param absPath

     *            写入文件的相对路径

     * 

     * @param wbName

     *            文件名

     */

    public static void writeWorkbook2007(XSSFWorkbook wb, String fileName) {

FileOutputStream fos = null;

try {

   fos = new FileOutputStream(fileName);

   wb.write(fos);

} catch (FileNotFoundException e) {

   log.error(new StringBuffer("[").append(e.getMessage()).append("]")

   .append(e.getCause()));

} catch (IOException e) {

   log.error(new StringBuffer("[").append(e.getMessage()).append("]")

   .append(e.getCause()));

} finally {

   try {

if (fos != null) {

   fos.close();

}

   } catch (IOException e) {

log.error(new StringBuffer("[").append(e.getMessage()).append(

"]").append(e.getCause()));

   }

}

    }



    /**

     * 

     * 功能：创建HSSFSheet工作簿

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param sheetName

     *            String

     * 

     * @return HSSFSheet

     */

    public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName) {

HSSFSheet sheet = wb.createSheet(sheetName);

sheet.setDefaultColumnWidth(12);

sheet.setGridsPrinted(false);

sheet.setDisplayGridlines(false);

return sheet;

    }



    /**

     * 

     * 功能：创建HSSFRow

     * 

     * @param sheet

     *            HSSFSheet

     * 

     * @param rowNum

     *            int

     * 

     * @param height

     *            int

     * 

     * @return HSSFRow

     */

    public static HSSFRow createRow(HSSFSheet sheet, int rowNum, Integer height) {

HSSFRow row = sheet.createRow(rowNum);

if (height != null) {



   row.setHeight(Short.parseShort(height.toString()));

}

return row;

    }



    /**

     * 

     * 功能：创建CellStyle样式

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param backgroundColor

     *            背景色

     * 

     * @param foregroundColor

     *            前置色

     * 

     * @param font

     *            字体

     * 

     * @return CellStyle

     */

    public static CellStyle createCellStyle(HSSFWorkbook wb,

   short backgroundColor, short foregroundColor, short halign,

   Font font) {

CellStyle cs = wb.createCellStyle();

cs.setAlignment(halign);

cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

cs.setFillBackgroundColor(backgroundColor);

cs.setFillForegroundColor(foregroundColor);

cs.setFillPattern(CellStyle.SOLID_FOREGROUND);

cs.setFont(font);

return cs;

    }



    /**

     * 

     * 功能：创建带边框的CellStyle样式

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param backgroundColor

     *            背景色

     * 

     * @param foregroundColor

     *            前置色

     * 

     * @param font

     *            字体

     * 

     * @return CellStyle
 [color=white] 原文转自: http://www.ij2ee.com/2011/05/05/java-poi-excel-%E6%93%8D%E4%BD%9C%E7%B1%BB-%E6%94%AF%E6%8C%812003%EF%BC%8C2007.html[/color]
     */

    public static CellStyle createBorderCellStyle(HSSFWorkbook wb,

   short backgroundColor, short foregroundColor, short halign,

   Font font) {

CellStyle cs = wb.createCellStyle();

cs.setAlignment(halign);

cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

cs.setFillBackgroundColor(backgroundColor);

cs.setFillForegroundColor(foregroundColor);

cs.setFillPattern(CellStyle.SOLID_FOREGROUND);

cs.setFont(font);

cs.setBorderLeft(CellStyle.BORDER_DASHED);

cs.setBorderRight(CellStyle.BORDER_DASHED);

cs.setBorderTop(CellStyle.BORDER_DASHED);

cs.setBorderBottom(CellStyle.BORDER_DASHED);

return cs;

    }



    public static void setBorder(HSSFWorkbook book, Integer top,

   Integer bottom, Integer left, Integer right) {

CellStyle style = book.createCellStyle();

if (top != null) {

   style.setBorderTop(Short.parseShort(top.toString()));

}

if (bottom != null) {

   style.setBorderBottom(Short.parseShort(bottom.toString()));

}

if (left != null) {

   style.setBorderLeft(Short.parseShort(left.toString()));

}

if (right != null) {

   style.setBorderRight(Short.parseShort(right.toString()));

}



    }



    /**

     * 

     * 功能：创建CELL

     * 

     * @param row

     *            HSSFRow

     * 

     * @param cellNum

     *            int

     * 

     * @param style

     *            HSSFStyle

     * 

     * @return HSSFCell

     */

    public static HSSFCell createCell(HSSFRow row, int cellNum, CellStyle style) {

HSSFCell cell = row.createCell(cellNum);

cell.setCellStyle(style);

return cell;

    }



    /**

     * 

     * 功能：合并单元格

     * 

     * @param sheet

     *            HSSFSheet

     * 

     * @param firstRow

     *            int

     * 

     * @param lastRow

     *            int

     * 

     * @param firstColumn

     *            int

     * 

     * @param lastColumn

     *            int

     * 

     * @return int 合并区域号码

     */

    public static int mergeCell(HSSFSheet sheet, int firstRow, int lastRow,

   int firstColumn, int lastColumn) {

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow,

firstColumn, lastColumn));

    }



    /**

     * 

     * 功能：创建字体

     * 

     * @param wb

     *            HSSFWorkbook

     * 

     * @param boldweight

     *            short

     * 

     * @param color

     *            short

     * 

     * @return Font

     */

    public static Font createFont(HSSFWorkbook wb, short boldweight,

   short color, short size) {

Font font = wb.createFont();

font.setBoldweight(boldweight);

font.setColor(color);

font.setFontHeightInPoints(size);

return font;

    }



    /**

     * 

     * 设置合并单元格的边框样式

     * 

     * @param sheet

     *            HSSFSheet

     * 

     * @param ca

     *            CellRangAddress

     * 

     * @param style

     *            CellStyle

     */

    public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,

   CellStyle style) {

for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {

   HSSFRow row = HSSFCellUtil.getRow(i, sheet);

   for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {

HSSFCell cell = HSSFCellUtil.getCell(row, j);

cell.setCellStyle(style);

   }

}

    }



    public static Font setFont(HSSFWorkbook workbook, int fontHeight,

   short boldWeight) {

// 字体

HSSFFont font = workbook.createFont();

// Font font = workbook.createFont();

font.setFontName("宋体");

font.setFontHeightInPoints((short) fontHeight);

font.setBoldweight(boldWeight);

return font;

    }



    public static boolean createExcel(String path) throws IOException {

File f = new File(path);

if (!f.exists()) {

   f.createNewFile();

   return true;

} else

   return true;

    }



    public static void setSheetHeadAndFoot(HSSFSheet sheet) {

HSSFHeader header = sheet.getHeader();

header.setCenter("Center Header");

header.setLeft("Left Header");

header.setRight(HSSFHeader.font("Stencil-Normal", "Italic")

+ HSSFHeader.fontSize((short) 16)

+ "Right w/ Stencil-Normal Italic font and size 16");



HSSFFooter footer = (HSSFFooter) sheet.getFooter();

footer.setRight("Page " + HSSFFooter.page() + " of "

+ HSSFFooter.numPages());

    }



    private static Logger log = Logger.getLogger(ExcelUtil2.class);

}