贴一段关于写excel的代码

yinleiyoung 2006-05-24 11:34:14

/*
* Created on May 23, 2006
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.poitest.samples;

/**
* @author ex_yinlei
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
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.Region;

/**
* Title: 
* Description:报表格式定义
* Copyright: Copyright (c) 2005
* Company: Ping An Insurance China Ltd.
* @author ex_fengyuanlong
* @version 1.0
*/
public class ExcelHSSFWorkbook {

private static String[] titlesname = { "信息电子文档" };

private static final int sheetAmount = 1;

public static HSSFWorkbook getExcelWorkBook(List excelList, int sheetNO,
int sheetAmount) throws Exception {

HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
HSSFCellStyle styleTitle = wb.createCellStyle();//建立标题的cell样式
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleTitle.setWrapText(true);

HSSFCellStyle styleData = wb.createCellStyle();//建立数据格的cell样式
styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleData.setWrapText(true);
styleData.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
styleData.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
styleData.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
styleData.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

//int sheetAmount = ExcelFileProperty.getSheetAmount();
HSSFSheet[] sheets = new HSSFSheet[sheetAmount];
HSSFRow[][] rowRecords = new HSSFRow[sheetAmount][];
Map recordMap = null;
int columnNo;//列号
String[] tableTitles = null;//表格的标题
Object cellData = null;//单元格数据
for (int sheetNo = sheetNO; sheetNo < sheetAmount; sheetNo++) {
sheets[sheetNo] = wb.createSheet(ExcelFileProperty
.getSheetName(sheetNo));//建立新的sheet对象
HSSFPrintSetup ps = sheets[sheetNo].getPrintSetup();
sheets[sheetNo].setAutobreaks(true);
ps.setFitWidth((short) 1);

if (excelList != null && excelList.size() > 0) {
rowRecords[sheetNo] = new HSSFRow[excelList.size() + 1];
//Create a row and put some cells in it. Rows are 0 based.
rowRecords[sheetNo][0] = sheets[sheetNo].createRow((short) 0);//建立新行作为标题行
tableTitles = ExcelFileProperty.getTableTitles(sheetNo);

/* 设置标题行 */
for (columnNo = 0; columnNo < tableTitles.length; columnNo++) {
sheets[sheetNo]
.setColumnWidth(
(short) columnNo,
(short) (tableTitles[columnNo].getBytes().length * 256));
HSSFCell cell = rowRecords[sheetNo][0]
.createCell((short) columnNo);//建立新cell
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(tableTitles[columnNo]);//设置cell的标题值
cell.setCellStyle(styleTitle);
}
/* 设置内容行 */
for (int recordNo = 1; recordNo <= excelList.size(); recordNo++) {

recordMap = (Map) excelList.get(recordNo - 1);

//Create a row and put the record in cells.
rowRecords[sheetNo][recordNo] = sheets[sheetNo]
.createRow((short) (recordNo));//建立新行作为记录行
for (columnNo = 0; columnNo < tableTitles.length; columnNo++) {
HSSFCell cell = rowRecords[sheetNo][recordNo]
.createCell((short) columnNo);//建立新cell
cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cellData = (Object) recordMap.get(ExcelFileProperty
.getCellKeys(sheetNo)[columnNo]);

if (cellData == null
|| cellData.toString().length() == 0) {
cellData = "";
}
cell.setCellValue(cellData.toString());//设置cell的值
cell.setCellStyle(styleData);
}

}
}
}

return wb;
}

}

...全文

190 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

yinleiyoung 2006-05-26

打赏
举报

<%@page language="java" contentType="application/vnd.ms-excel;charset=gb2312"
import="org.apache.poi.hssf.usermodel.HSSFWorkbook"
%>
<%
String contentFileName = (String)request.getAttribute("filename");

if(contentFileName ==null || contentFileName.length()==0){
contentFileName="myExcel";
}

response.reset();

response.setContentType("application/octet-stream; charset=iso-8859-1");
contentFileName =new String(contentFileName.getBytes("GB2312"),"iso8859-1");

response.addHeader("Content-disposition", "attachment; filename=\"" + contentFileName + ".xls\"");
HSSFWorkbook workbook=(HSSFWorkbook)request.getAttribute("message");
workbook.write(response.getOutputStream());
%>

sole_lodestar 2006-05-26

打赏
举报

啥意思？

AreamArgentateOfWing 2006-05-26

打赏
举报

看了上面代码,个人觉得jxl设置比poi简单...

Knight320 2006-05-26

打赏
举报

jxl.jar 和他有什么区别，那个更好用？

fzlotuscn 2006-05-24

打赏
举报

up!

yinleiyoung 2006-05-24

打赏
举报

测试：
/*
* Created on May 23, 2006
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.poitest.samples;

/**
* @author ex_yinlei
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;
import java.util.ArrayList;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar.
public class PoiTest0 {
public static void main(String[] args) throws Exception {

int sheeNo = 0;
int sheetAmount = 1;

FileOutputStream fos = new FileOutputStream("d://test.xls");
List dataList = new ArrayList();

Map dateMap1 = new HashMap();
dateMap1.put("DEPT_NAME","E00000001");
dateMap1.put("EMPNO","922222221");
dateMap1.put("EMP_NAME","yyy1");
dateMap1.put("TYPE","事假");
dateMap1.put("START_TIME","2006-05-20");
dateMap1.put("END_TIME","2006-05-21");

dataList.add(dateMap1);

Map dateMap2 = new HashMap();
dateMap2.put("DEPT_NAME","E00000002");
dateMap2.put("EMPNO","922222222");
dateMap2.put("EMP_NAME","yyy2");
dateMap2.put("TYPE","请假");
dateMap2.put("START_TIME","2006-05-24");
dateMap2.put("END_TIME","2006-05-24");

dataList.add(dateMap2);

Map dateMap3 = new HashMap();
dateMap3.put("DEPT_NAME","E00000003");
dateMap3.put("EMPNO","922222223");
dateMap3.put("EMP_NAME","yyy3");
dateMap3.put("TYPE","病假");
dateMap3.put("START_TIME","2006-05-25");
dateMap3.put("END_TIME","2006-05-26");

dataList.add(dateMap3);

HSSFWorkbook wb = ExcelHSSFWorkbook.getExcelWorkBook(dataList,sheeNo,sheetAmount);

wb.write(fos);

fos.close();
}
}

yinleiyoung 2006-05-24