最近响应公司的号召,要把一些特定的数据导出成excel格式(.xls),在度娘和古哥的大力帮助下,得知 poi 功能极其强大,是干excel 的一把手,故 写mode一个,闲言碎语不多讲,直接进入正题,贴出主要代码大家共同学习,如有更好的方法,可以发Email给我讨论,共同进步 导入的核心代码:
/**
*
*/
package com.zhaokuo;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* @author 赵阔 2012-6-29
* QQ:275669668
* Email:zhaokuo719@gmail.com
*/
public class ImportExcel {
public List<List<Object>> readExcel2003(String path) throws Exception {
List<List<Object>> list = new ArrayList<List<Object>>();
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
HSSFSheet sheet = workbook.getSheetAt(0);
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i < sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new ArrayList<Object>();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
DecimalFormat nf = new DecimalFormat("0.00");
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("Generl".equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "未知";
break;
default:
value = cell.toString();
}
if (value.equals("")) {
value = "未知";
}
if (null == value) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}
}
导出核心代码:
/**
*
*/
package com.zhaokuo;
import java.io.FileOutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.util.HSSFColor;
/**
* @author 赵阔 2012-6-29 QQ:275669668 Email:zhaokuo719@gmail.com
*/
public class ExportExcel {
public static List<String> tableHeader;
// 创建sheet
@SuppressWarnings("unchecked")
public void createExcelSheet(List list, String fileRealPath)
throws Exception {
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet("sheet");
HSSFHeader header = sheet.getHeader();
header.setCenter("sheet");
HSSFRow headerRow = sheet.createRow(0);
HSSFCellStyle headstyle = workBook.createCellStyle();
HSSFFont headfont = workBook.createFont();
headfont.setColor(HSSFColor.BLACK.index);
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headstyle.setFont(headfont);
for (int i = 0; i < tableHeader.size(); i++) {
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellStyle(headstyle);
// 设置cell的值
headerCell.setCellValue(tableHeader.get(i));
headerCell.setCellStyle(headstyle);
}
int rowIndex = 1;
for (int i = 1; i < list.size(); i++) {
List<String> list2 = (List<String>) list.get(i);
HSSFRow row = sheet.createRow(rowIndex);
for (int q = 0; q < list2.size(); q++) {
// 创建第i个单元格
HSSFCell cell = row.createCell(q);
cell.setCellValue(list2.get(q).replace("未知", ""));
sheet.setColumnWidth(q, (80 * 50));
}
rowIndex++;
}
FileOutputStream fos = new FileOutputStream(fileRealPath);
sheet.setGridsPrinted(true);
HSSFFooter footer = sheet.getFooter();
footer.setRight("Page " + HSSFFooter.page() + " of "
+ HSSFFooter.numPages());
workBook.write(fos);
fos.close();
}
}
此项目里面包涵了servlet 的上传和下载 您可以先看看
http://blog.csdn.net/zhaokuo719/article/details/7703074
完整的代码 请到 http://download.csdn.net/detail/zhaokuo719/4401391 下载;虽然分数有点高;俗话说只买对的不嫌贵的;肯定物有所值