POI处理Excel 的例子。
1 读取Excel见附件中 ReadExce/src/test/ReadExcel.java
2 写Excel的简单使用:
package test3;
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
public class WriteExcel {
/**
* 写Excel的简单使用
*/
public static void main(String[] args) throws Exception {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet名字");
// 设置excel每列宽度
sheet.setColumnWidth((short)0, (short)4000); //设置第0列的宽
sheet.setColumnWidth((short)1, (short)3500); //设置第1列的宽
// 创建字体样式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBottomBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);// 设置字体
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell = row.createCell((short)0);
// 合并单元格(startRow,endRow,startColumn,endColumn)
sheet.addMergedRegion(new Region(0, (short)0, 0, (short)2));
// 给Excel的单元格设置样式和赋值
cell.setCellStyle(style);
cell.setCellValue("hello world");
// 设置单元格内容格式
HSSFCellStyle style1 = wb.createCellStyle();
style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
style1.setWrapText(true);// 自动换行
row = sheet.createRow(1);
//设置单元格的样式格式
cell = row.createCell((short)0);
cell.setCellStyle(style1);
cell.setCellValue(new Date());
FileOutputStream os = new FileOutputStream("e:\\workbook.xls");
wb.write(os);
os.close();
}
}
这个Excel写在了本地,如果要实现网络下载Excel 则将创建的excel写到网络流中:
添加如下代码:
response.reset();//可以加也可以不加 这个是httpServletResponse
response.setContentType("application/x-download");
String filedisplay = "统计表.xls";
String Agent = request.getHeader("User-Agent");
try { //不同浏览器文件名编码不一样
if (null != Agent) {
Agent = Agent.toLowerCase();
if (Agent.indexOf("firefox") != -1) {
filedisplay = new String(filedisplay.getBytes(), "iso8859-1");
} else {
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
}
} else {
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.addHeader("Content-Disposition", "attachment;filename="
+ filedisplay);
///
// 将数据写回前台
OutputStream outp = null;
try {
outp = response.getOutputStream();
wb.write(outp); //wb 就是HSSFWorkbook 实例
outp.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outp.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}