原文转自: 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);
}