JAVA读取与导出EXCEL（poi）

来源：互联网发布：京瓷1125mfp 网络配置编辑：程序博客网时间：2024/05/20 06:56

JAVA读取与导出EXCEL

用到的jar包有：

poi-3.10.1-20140818.jar

poi-ooxml-3.10.1-20140818.jar

poi-ooxml-schemas-3.10.1-20140818.jar

xmlbeans-2.6.0.jar

dom4j-1.6.1.jar

相关的代码如下：

package com.poi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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;

public class PoiRead {
public static void main(String[] args) {
PoiRead t = new PoiRead();
t.readExecl("d:/111.xlsx");
}
/**
* 创建兼容模式工作簿book
* @param path EXCEL文件路径
*/
public void readExecl(String path){
InputStream is;
Workbook book = null;
//兼容2003以及2007以上版本
try {
is = new FileInputStream(path);
try {
book = new XSSFWorkbook(is);//2007 后缀名字xlsx
} catch (Exception e) {
book = new HSSFWorkbook(is);//2003 后缀名字xls
}
readExcel2003_2007(book);
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取EXCEL
* @param book 工作簿
*/
public void readExcel2003_2007(Workbook book){
// 循环工作表Sheet
for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
Sheet hssfSheet = book.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
//循环行Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
Row hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
//循环列Cell
for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++){
Cell hssfCell = hssfRow.getCell(cellNum);
if(hssfCell==null){
continue;
}
System.out.println(getValue(hssfCell));
}
}
}
}
/**
* 获取EXCEL单元格内的值
* @param hssfCell
* @return
*/
private String getValue(Cell hssfCell) {
DecimalFormat df = new DecimalFormat("0");// 格式化数字
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");// 格式化日期字符串
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
if ("@".equals(hssfCell.getCellStyle().getDataFormatString())) {
return String.valueOf(df.format(hssfCell.getNumericCellValue()));
} else if ("General".equals(hssfCell.getCellStyle().getDataFormatString())) {
return String.valueOf(df.format(hssfCell.getNumericCellValue()));
} else {
//返回时间格式的值
return String.valueOf(sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())));
}
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 导出为EXCEL
* @param path 导出路径
* @param list 导出的数据
* @param tousuType 举例用来区分统计的数据类型
*/
public void writeExcel(String path,List<Map> list,String tousuType){
OutputStream out;
tousuType=tousuType.toLowerCase();
try {
// 获取总列数
int CountColumnNum = 32;
String title = "";
if("r1".equals(tousuType)){
title="全部投诉";
}else if("r2".equals(tousuType)){
title="否认定制投诉";
}else if("r3".equals(tousuType)){
title="定制争议投诉";
}else if("r4".equals(tousuType)){
title="商品未到账投诉";
}
// 创建Excel文档
HSSFWorkbook book = new HSSFWorkbook();//2003
// sheet 对应一个工作页
HSSFSheet sheet = book.createSheet("Sheet1");
HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
HSSFCell firstrowName = firstrow.createCell(0);
firstrowName.setCellValue(title);
sheet.addMergedRegion(new Region(0, (short)0, 0, (short)32));//合并单元格
CellStyle cellStyle = book.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//创建一个新的字体,并将其改变
Font font = book.createFont();
//使用像素为单位设置其字体高度
font.setFontHeightInPoints((short)16);
//设置字体名称,使用那种样式的字体显示,也就是你的C:\Windows\Fonts 的字体的名称
font.setFontName("微软雅黑");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.BLACK.index);//字体颜色
//设置是否使用斜体
// font.setItalic(true);
//设置是否删除线通过字体
// font.setStrikeout(true);
//将新的Font设置给CellStyle,所以需要创建一个新的Font
// CellStyle style = wb.createCellStyle();
// style.setFont(font);
cellStyle.setFont(font);
setBorder(cellStyle);
firstrowName.setCellStyle(cellStyle);

CellStyle cellStyle2 = book.createCellStyle();
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font1 = book.createFont();
font1.setFontHeightInPoints((short) 10); // 字体高度
font1.setFontName("微软雅黑"); // 字体
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度
cellStyle2.setFont(font1);
cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充单元格
cellStyle2.setFillForegroundColor(HSSFColor.YELLOW.index);
setBorder(cellStyle2);
// cellStyle2.setFillBackgroundColor(HSSFColor.YELLOW.index);

HSSFRow row2 = sheet.createRow(1);
Cell[] cell2 = new Cell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0]="公司";
for(int i=1;i<=31;i++){
names[i]=i+"日";
}
for (int j = 0; j < CountColumnNum; j++) {
cell2[j] = row2.createCell(j);
HSSFRichTextString ts = new HSSFRichTextString(names[j]);
// ts.applyFont(font1);
cell2[j].setCellValue(ts);
cell2[j].setCellStyle(cellStyle2);
}
CellStyle cellStyle3 = book.createCellStyle();
cellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font2 = book.createFont();
font2.setFontHeightInPoints((short) 10); // 字体高度
font2.setFontName("微软雅黑"); // 字体
cellStyle3.setFont(font2);
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充单元格
cellStyle3.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
setBorder(cellStyle3);
CellStyle cellStyle4 = book.createCellStyle();
cellStyle4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle4.setFont(font2);
setBorder(cellStyle4);
for (int i = 0; i < list.size(); i++) {
// 创建一行
HSSFRow row = sheet.createRow(i + 2);
// 得到要插入的每一条记录
Map map = list.get(i);
HSSFCell xh1 = row.createCell(0);
xh1.setCellValue(map.get("CPNAME").toString());
xh1.setCellStyle(cellStyle3);
for (int colu = 1; colu < CountColumnNum; colu++) {
// 在一行内循环
String key="DAY"+colu;
HSSFCell xh = row.createCell(colu);
xh.setCellValue(map.get(key).toString());
xh.setCellStyle(cellStyle4);
}
}
// 创建文件输出流，准备输出电子表格
out = new FileOutputStream("d:/123.xls");
book.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
System.out.println("数据导出成功");
}
/**
* 设置单元格的边框
* @param cellStyle
*/
public void setBorder(CellStyle cellStyle){
cellStyle.setBorderBottom(CellStyle.SOLID_FOREGROUND);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//黑色
cellStyle.setBorderLeft(CellStyle.SOLID_FOREGROUND);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());//黑色
cellStyle.setBorderRight(CellStyle.SOLID_FOREGROUND);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());//黑色
cellStyle.setBorderTop(CellStyle.SOLID_FOREGROUND);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());//黑色
}
/**
* Action中的导出为EXCEL功能
*/
/*public void excelOut() {
//获取要导出的的数据
List<Map> list = (List<Map>)ServletActionContext.getRequest().getSession().getAttribute("tousuList");
String tousuType = ServletActionContext.getRequest().getSession().getAttribute("tousuType").toString();
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/vnd.ms-excel");
String time = DateUtil.dateToString(new Date(), "yyyyMMddHHmmss");
response.setHeader("content-disposition", "attachment; filename=" + time + ".xls"); // 设置下载的文件名
OutputStream output = null;
try {
output = (OutputStream) response.getOutputStream();
try {
writeExcel(list, tousuType, output);//此时只需将上面的writeExcel方法传入参数改改即可
} catch (Exception e) {
e.printStackTrace();
}
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}*/
}

1 0