/* *******************************************************************************
*
* Copyright(c) 2005 by E4E Unicom HuaJian.
*
* All rights reserved.
*
* *******************************************************************************
* File Name : test.java
* Description :
* -------------------------------------------------------------------------------
* No. Date Revised by Description
* 0 Dec 14, 2006 chenk Created
* *******************************************************************************/
package cn.mfinance.customerservice.web.run.count.vast;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
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 cn.mfinance.common.Utility;
import cn.mfinance.unicom.bj.support.vo.VastShopDayReportVO;
public class CreateExcelFile {
private static String outputFile = "C:/test.xls";
private static String fileToBeRead = "c://test.xls";
private static Log logger = LogFactory.getLog(CreateExcelFile.class);
/*
* 本方法封装得还不够易用,在改进(改到很容易生成不同类型的Excel报表为止)
*/
public void CreateExcel(OutputStream ops, List list, String type) {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建两个工作表
HSSFSheet sheet1 = workbook.createSheet("购卡财务日报");
HSSFSheet sheet2 = workbook.createSheet("购卡业务日报");
String[] operationCardHeader = { "联通华建网络有限公司", "充值卡业务日报表", "充值卡信息", "日期", "期初余额", "进货信息", "销售信息", "结余量", "充值卡名称", "进货价",
"数量", "金额", "数量", "金额", "数量", "进货金额", "出货金额", "数量", "金额", "合计", };
// 给工作表前14列定义列宽
sheet1.setColumnWidth((short)0,(short)4000);
sheet1.setColumnWidth((short)1,(short)4000);
sheet1.setColumnWidth((short)2,(short)4000);
sheet1.setColumnWidth((short)3,(short)4000);
sheet1.setColumnWidth((short)4,(short)4000);
sheet1.setColumnWidth((short)5,(short)4000);
sheet1.setColumnWidth((short)6,(short)4000);
sheet1.setColumnWidth((short)7,(short)4000);
sheet1.setColumnWidth((short)8,(short)4000);
sheet1.setColumnWidth((short)9,(short)4000);
sheet1.setColumnWidth((short)10,(short)4000);
sheet1.setColumnWidth((short)11,(short)4000);
sheet1.setColumnWidth((short)12,(short)4000);
sheet1.setColumnWidth((short)13,(short)4000);
sheet1.setHorizontallyCenter(true);
// 合并一些区域
sheet1.addMergedRegion(new Region(0, (short) 0, 0, (short) 2));
sheet1.addMergedRegion(new Region(1, (short) 0, 1, (short) 2));
sheet1.addMergedRegion(new Region(2, (short) 0, 2, (short) 13));
sheet1.addMergedRegion(new Region(3, (short) 0, 3, (short) 2));
sheet1.addMergedRegion(new Region(3, (short) 3, 4, (short) 4));
sheet1.addMergedRegion(new Region(3, (short) 5, 3, (short) 6));
sheet1.addMergedRegion(new Region(3, (short) 7, 3, (short) 8));
sheet1.addMergedRegion(new Region(3, (short) 9, 3, (short) 11));
sheet1.addMergedRegion(new Region(3, (short) 12, 3, (short) 13));
sheet1.addMergedRegion(new Region(4, (short) 0, 4, (short) 1));
// 新建两个标题
HSSFRow row = sheet1.createRow(0);
HSSFCell cell = row.createCell((short) 0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(setTitleStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[0]));
row = sheet1.createRow(1);
cell = row.createCell((short) 0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(setTitleStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[1]));
// 写入一条黄色行
row = sheet1.createRow(2);
cell = row.createCell((short) 0);
cell.setCellStyle(setForeYellowStyle(workbook));
// 写入表头
row = sheet1.createRow(3);
row.setHeight((short) 400);// 400是行高值
cell = row.createCell((short) 0);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[2]));
row.createCell((short) 1).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
row.createCell((short) 2).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 3);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[3]));
row.createCell((short) 4).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 5);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[4]));
row.createCell((short) 6).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 7);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[5]));
row.createCell((short) 8).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 9);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[6]));
row.createCell((short) 10).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
row.createCell((short) 11).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 12);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[7]));
row.createCell((short) 13).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
row = sheet1.createRow(4);
row.setHeight((short) 400);// 400是行高值
cell = row.createCell((short) 0);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[8]));
row.createCell((short) 1).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 2);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[9]));
row.createCell((short) 3).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
row.createCell((short) 4).setCellStyle(setDateStyle(workbook));// 充数的cell,完全是为了显示风格
cell = row.createCell((short) 5);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[10]));
cell = row.createCell((short) 6);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[11]));
cell = row.createCell((short) 7);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[12]));
cell = row.createCell((short) 8);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[13]));
cell = row.createCell((short) 9);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[14]));
cell = row.createCell((short) 10);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[15]));
cell = row.createCell((short) 11);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[16]));
cell = row.createCell((short) 12);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[17]));
cell = row.createCell((short) 13);
cell.setCellStyle(setHeaderStyle(workbook));
cell.setCellValue(new HSSFRichTextString(operationCardHeader[18]));
List<List> values = new ArrayList<List>(); // 表格内容
// 将list中的数据转换一下
values = ConvertDate(list, values, type);
// 写入单元格内容
for (int i = 0; i < values.size(); i++) {
sheet1.addMergedRegion(new Region(i + 5, (short) 0, i + 5, (short) 1));
sheet1.addMergedRegion(new Region(i + 5, (short) 3, i + 5, (short) 4));
row = sheet1.createRow(i + 5); // 建立新的行
row.setHeight((short) 400);// 400是行高值
List lRecord = (ArrayList) values.get(i);
for (int j = 0; j < 14; j++) {
if (j == 1 || j == 4) {
cell = row.createCell((short) j); // 建立新的列
cell.setCellStyle(setDateStyle(workbook));
} else {
if (j == 0) {
cell = row.createCell((short) j); // 建立新的列
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(setDateStyle(workbook));
cell.setCellValue(new HSSFRichTextString((String)lRecord.get(j)));
} else {
if (j == 3) {
cell = row.createCell((short) j); // 建立新的列
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(setDateStyle(workbook));
cell.setCellValue(new HSSFRichTextString(Utility.toDatetime((Date)lRecord.get(j)).substring(0, 10)));
} else {
cell = row.createCell((short) j); // 建立新的列
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(setDateStyle(workbook));
cell.setCellValue((Integer)lRecord.get(j));
}
}
}
}
}
// 合计部分单元格(计算的公式部分再找资料看看)
// row.createCell((short) 0).setCellValue(new HSSFRichTextString(operationCardHeader[18]));
// StringBuffer formula = new StringBuffer("SUM(F2:F");
// formula.append(values.size() + 5)
// .append(")");
// row.createCell((short) 5).setCellFormula(formula.toString());
// 设置页眉
HSSFHeader header = sheet1.getHeader();
header.setCenter(new String("日报表".getBytes("Unicode"), "Unicode"));
header.setLeft(new String("日报表".getBytes("GBK"), "8859_1"));
header.setRight(HSSFHeader.date());
// 设置页脚
HSSFFooter footer = sheet1.getFooter();
footer.setCenter("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
if (ops == null) {
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
} else {
workbook.write(ops);
ops.flush();
ops.close();
}
logger.debug("文件生成...");
} catch (Exception e) {
logger.debug("已运行 xlCreate() : " + e);
}
}
private HSSFCellStyle setTitleStyle(HSSFWorkbook workbook) {
// 先定义一个字体对象
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
// font.setColor(HSSFFont.COLOR_RED);
font.setFontHeightInPoints((short) 12); // 字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
// 定义表头单元格格式
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font); // 单元格字体
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
return style;
}
private HSSFCellStyle setForeYellowStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
// 设定此样式的的背景颜色填充
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// HSSFCellStyle.BIG_SPOTS // HSSFCellStyle.FINE_DOTS
// HSSFCellStyle.SPARSE_DOTS等
style.setFillBackgroundColor(HSSFColor.AQUA.index);// 设定单元格背景颜色
style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置单元格前端显示颜色
// 定义新的调色板以便定义自己的颜色
// HSSFPalette palette = workbook.getCustomPalette();
// 替换一个默认调色板中已经定义的颜色
// palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 230, (byte) 230, (byte) 255);
// 使用刚定义的颜色为填充色
// cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
// cellStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);
return style;
}
private HSSFCellStyle setHeaderStyle(HSSFWorkbook workbook) {
// 先定义一个字体对象
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12); //字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗
// 定义数据单元格格式
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 表格细边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中水平对齐方式
// 定义数据格式
HSSFDataFormat df = workbook.createDataFormat();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
style.setDataFormat(df.getFormat("#,##0.0"));
style.setDataFormat(df.getFormat("0.0"));
return style;
}
private HSSFCellStyle setDateStyle(HSSFWorkbook workbook) {
// 定义数据单元格格式
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 表格细边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中水平对齐方式
// 定义数据格式
HSSFDataFormat df = workbook.createDataFormat();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
style.setDataFormat(df.getFormat("#,##0.0"));
// style.setDataFormat(df.getFormat("0.0"));
// style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
return style;
}
private List ConvertDate(List list, List values, String type) {
if (type.equals("fDay")) {
for (int j = 0; j < list.size(); j++) {
VastShopDayReportVO vo = (VastShopDayReportVO)list.get(j);
List data = new ArrayList();
data.add(vo.getMdseName()); // 1.充值卡名称
data.add(Integer.valueOf(1)); // 充数的
data.add(vo.getMdsePrice()); // 2.进货价
data.add(vo.getDay()); // 3. 日期
data.add(Integer.valueOf(1)); // 充数的
data.add(vo.getStockQuantity()); // 4.开始库存数量
data.add(vo.getSoldPrice() * vo.getStockQuantity()); // 5.
data.add(vo.getStockinQuantity()); // 6
data.add(vo.getMdsePrice() * vo.getStockinQuantity()); // 7
data.add(vo.getStockQuantity() + vo.getStockinQuantity() - vo.getRemainStock()); // 8
data.add(vo.getMdsePrice() * (vo.getStockQuantity() + vo.getStockinQuantity() - vo.getRemainStock())); // 9
data.add(vo.getSoldPrice() * (vo.getStockQuantity() + vo.getStockinQuantity() - vo.getRemainStock())); // 10
data.add(vo.getRemainStock()); // 11
data.add(vo.getMdsePrice() * vo.getRemainStock()); // 12
values.add(data);
}
}
if (type.equals("fMonth")) {
}
if (type.equals("oDay")) {
}
if (type.equals("oWeek")) {
}
if (type.equals("oMonth")) {
}
return values;
}
/**
*
* 读取excel,遍历各个小格获取其中信息,并判断其是否是手机号码,并对正确的手机号码进行显示
*
* 注意: 1.sheet, 以0开始,以workbook.getNumberOfSheets()-1结束 2.row,
* 以0开始(getFirstRowNum),以getLastRowNum结束 3.cell,
* 以0开始(getFirstCellNum),以getLastCellNum结束, 结束的数目不知什么原因与显示的长 度不同,可能会偏长
*
*/
public void readExcel() {
// 将被表示成1.3922433397E10的手机号转化为13922433397,不一定是最好的转换方法
DecimalFormat df = new DecimalFormat("#");
try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
System.out.println("===SheetsNum===" + workbook.getNumberOfSheets());// 获取sheet数
for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
if (null != workbook.getSheetAt(numSheets)) {
HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
System.out.println("+++getFirstRowNum+++" + aSheet.getFirstRowNum());//
System.out.println("+++getLastRowNum+++" + aSheet.getLastRowNum());
for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
if (null != aSheet.getRow(rowNumOfSheet)) {
HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
System.out.println(">>>getFirstCellNum<<<" + aRow.getFirstCellNum());
System.out.println(">>>getLastCellNum<<<" + aRow.getLastCellNum());
for (short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
if (null != aRow.getCell(cellNumOfRow)) {
HSSFCell aCell = aRow.getCell(cellNumOfRow);
int cellType = aCell.getCellType();
System.out.println(cellType);
switch (cellType) {
case 0:// Numeric
String strCell = df.format(aCell.getNumericCellValue());
System.out.println(strCell);
break;
case 1:// String
strCell = aCell.getStringCellValue();
System.out.println(strCell);
break;
default:
System.out.println("格式不对不读");// 其它格式的数据
}
}
}
}
}
}
}
} catch (Exception e) {
System.out.println("ReadExcelError" + e);
}
}
public static void main(String[] args) {
/* CreateExcelFile poi = new CreateExcelFile();
poi.CreateExcel();*/
}
}
/* *******************************************************************************
*
* Copyright(c) 2005 by E4E Unicom HuaJian.
*
* All rights reserved.
*
* *******************************************************************************
* File Name : UserExcelViewAction.java
* Description :
* -------------------------------------------------------------------------------
* No. Date Revised by Description
* 0 Dec 11, 2006 chenk Created
* *******************************************************************************/
package cn.mfinance.customerservice.web.run.count.vast;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import cn.mfinance.customerservice.web.common.EJBHelper;
import cn.mfinance.unicom.bj.support.interfaces.BJSupportManager;
/**
* MyEclipse Struts Creation date: 10-31-2006
*
* XDoclet definition:
*
* @struts.action validate="true"
*/
public class ListStatisticReportAction
extends Action {
/*
* Generated Methods
*/
/**
* Method execute
*
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
private static Log logger = LogFactory.getLog(ListStatisticReportAction.class);
public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
try {
BJSupportManager manager = EJBHelper.getQueryTradeLogsSupport();
List result = null;
String type = request.getParameter("type");
String date = request.getParameter("date");
String weekday = request.getParameter("weekday");
String month = request.getParameter("month");
String year = request.getParameter("year");
// 现在的后台程序先假设前台页面上已经对数据进行了效验,就是说没有为空值的情况
/** ********************* f为前缀的为财务报表 o为前缀的为业务报表 ************************* */
if (type.equals("fDay") || type.equals("oDay")) {
StringTokenizer st = new StringTokenizer(date, "-");
result = manager.getVastDateReport(Integer.parseInt(st.nextToken()), Integer.parseInt(st
.nextToken()) - 1, Integer.parseInt(st.nextToken())); // 取到数据后,就根据报表格式算出一个数据List交给前台显示
}
if (type.equals("oWeek")) {
StringTokenizer st = new StringTokenizer(weekday, "-");
result = manager.getVastWeekReport(Integer.parseInt(st.nextToken()), Integer.parseInt(st
.nextToken()) - 1, Integer.parseInt(st.nextToken()));
}
if (type.equals("fMonth")|| type.equals("oMonth")) {
result = manager.getVastMonthReport(Integer.parseInt(year), Integer.parseInt(month) - 1); // 取到数据后,就根据报表格式算出一个数据List交给前台显示
}
if (request.getParameter("download") == null) {
request.setAttribute("type", type);
request.setAttribute("date", date);
request.setAttribute("weekday", weekday);
request.setAttribute("month", month);
request.setAttribute("year", year);
request.setAttribute("list", result == null ? new ArrayList() : result);
return mapping.findForward("success");
} else {
// 将工作薄输出到输出流
CreateExcelFile poi = new CreateExcelFile();
response.setContentType(CONTENT_TYPE);
response.setHeader("Content-Disposition", "attachment;filename="
+ new String("report.xls".getBytes(), "iso-8859-1"));
ServletOutputStream sops = response.getOutputStream();
poi.CreateExcel(sops, result, type);
// 也可输出成xls文件
File file = new File("C:/Documents and Settings/chenk/桌面/test.xls");
FileOutputStream fileOut = new FileOutputStream(file);
poi.CreateExcel(fileOut, result, type);
return null;
}
} catch (Exception e) {
return mapping.findForward("error");
}
}
}