听说下周三交货了，可是今天需求还在不停的变，你说一个报表，怎么就那么多要求，特别是那个列头变来变去，一会动态生成，一会成对出现，。。。
刚刚可紧张了，听说营运部的老大是个男人婆，可凶了，所以我很认真的敲打每个代码，一遍祈祷我写的都是有用的，一遍很努力的去想下一步该怎么做，
因为我怕自己做不好，被凶。哈哈 终于半个小时后，报表变成了我想要的效果。啦啦啦啦啦， 我是卖报的小行家
。。。给她取个名字吧 叫 第一个测试版本
package javaservlets.production.mpexcel;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import javaservlets.common.util.Debug;
import javaservlets.common.util.SimpleServlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.CellFormat;
import jxl.Workbook;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
@SuppressWarnings({ "unchecked", "serial" })
public class MP_PC_jxl extends HttpServlet {
@SuppressWarnings({ "unused", "deprecation" })
private void modiFormulaCell(WritableSheet dataSheet, int col, int row, int startPos, int endPos, CellFormat format)
throws RowsExceededException, WriteException {
String f = getFormula(col, row, startPos, endPos);
WritableCell cell = dataSheet.getWritableCell(col, row);
Formula lbl = new Formula(col, row, f);
if(null != format) {
lbl.setCellFormat(format);
} else {
lbl.setCellFormat(cell.getCellFormat());
}
dataSheet.addCell(lbl);
}
private String getFormula(int col, int row, int startPos, int endPos)
throws RowsExceededException, WriteException
{
char base = 'A';
char cc = base;
StringBuffer formula = new StringBuffer(128);
formula.append("SUM(");
if (col <= 25) {
cc = (char) (col % 26 + base);
formula.append(cc).append(startPos).append(":")
.append(cc).append(endPos).append(")");
}
return formula.toString();
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String no = null;
Enumeration enumeration = request.getParameterNames();
while (enumeration.hasMoreElements()) {
String paramName = (String) enumeration.nextElement();
if (Debug.Level > 0) {
System.out.println("request parameter [" + paramName + "]=" +
request.getParameter(paramName));
}
}
no = request.getParameter("MP_ID");
OutputStream out = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=PC_List.xls");
String myFmt=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
System.out.println("開始創建excel，創建時間為："+myFmt);
WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
WritableSheet s = w.createSheet("PC list", 0);
s.getSettings().setDefaultColumnWidth(20); //设置列的宽度默认为15
//s.getSettings().setDefaultRowHeight(h) 設置默認行高
//s.getSettings().setShowGridLines(true);//設置顯示grid網格 true
s.setRowView(3, 750); //设置第i+1 行的高度
con = SimpleServlet.axaptaCP.getConnection();
stmt = con.createStatement();
//标记第几个结果集
int tag = 0 ;
boolean label = true;
stmt.execute("{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
System.out.println("調用存儲過程：--->"+"{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
//初始化数据
String myvalue = null;
Date mydate = null ;
int testint = 0;
int k = 4;
//获取表头显示信息
Statement stmt2 = null;
ResultSet rs2 = null;
stmt2 = con.createStatement();
rs2 = stmt2.executeQuery("select PROJECT_ID, PROJECT_DESC from " + SimpleServlet.axaptaCP.elbaseDB + ".DBO.mp_header where mp_id = '" + no + "'");
//获取sku信息
ArrayList skuList = (ArrayList<String>)request.getAttribute("columnDisp");
ArrayList sku = (ArrayList<String>)request.getAttribute("column");
int mycol = skuList.size();
//************************************************STYLE*****************************************************************************
/*
*
* 設置自動換行
* 水平居中顯示
* 垂直居中顯示
* 設置邊框樣式
*
* */
jxl.write.WritableCellFormat wcsB0 = new jxl.write.WritableCellFormat();
wcsB0.setWrap(true);
wcsB0.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcsB0.setAlignment(jxl.format.Alignment.CENTRE);
wcsB0.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
wcsB.setWrap(true);
wcsB.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
jxl.write.WritableCellFormat wcsB2 = new jxl.write.WritableCellFormat();
wcsB2.setAlignment(jxl.format.Alignment.CENTRE);
wcsB2.setWrap(true);
wcsB2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
jxl.write.WritableFont mywf = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat mywc = new jxl.write.WritableCellFormat(mywf);
jxl.write.WritableFont wfct = new jxl.write.WritableFont(WritableFont.TIMES,12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wcsBa = new jxl.write.WritableCellFormat(wfct);
wcsBa.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
wcsBa.setAlignment(jxl.format.Alignment.CENTRE);
wcsBa.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
// table header
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
wchB.setAlignment(jxl.format.Alignment.CENTRE);
//wchB.setBackground(jxl.format.Colour.GRAY_25);
// 10 号 红色字体 加粗 居右显示
jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
jxl.write.WritableCellFormat wchB1 = new jxl.write.WritableCellFormat(wfc1);
wchB1.setAlignment(jxl.format.Alignment.RIGHT);
wchB1.setBackground(jxl.format.Colour.GRAY_25);
//***********************************************************************************************************************************
while(rs2.next()){
s.mergeCells(3, 0, 6, 1);
s.addCell(new Label(3, 0, rs2.getString(1), wchB));
s.mergeCells(7, 0, 7+mycol, 1);
s.addCell(new Label(7, 0, rs2.getString(2), wchB));
}
// table header about time and date
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");//yyy-MM-dd hh:mm:ss 年月日时分秒
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
wcfDF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// format number
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
wcfN.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
wcfN.setBackground(jxl.format.Colour.SKY_BLUE);
/*
* 參數明細 0(='_'=)0
*
* isHaveCosmeticcode 標記cosmeticcode是否為空
* mark 標記動態生陳列是否-2
* va 标记sku自增数
*
* */
String isHaveCosmeticcode = null;
String mark = null;
int column = 20;
int va = 0;
while(label){
int i = 0;
i = stmt.getUpdateCount();
boolean up_label = false;
if(i != -1){
up_label = stmt.getMoreResults();//移动到此SQLServerStatement 对象的下一个结果。如果返回一个结果及
continue;
}
boolean flag_label = false;
rs = stmt.getResultSet();
if(rs != null){
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if(tag ==0){
while(rs.next()){
for(int j=0;j<columnCount;j++){
System.out.println("columnCount-->"+columnCount);
if(j ==0){
System.out.println("test 類型"+rsmd.getColumnType(j+1));
s.mergeCells(0, 0, 1, 0);
s.addCell(new jxl.write.DateTime(13+mycol, 2,rs.getDate(j+1), wcfDF));
}else if(j==1){
s.setColumnView(0, 25);
s.addCell(new Label(0, 0,rs.getString(j+1), wcsBa));
}else if(j ==2){
isHaveCosmeticcode = rs.getString(j+1);
}
}
}
}else if(tag == 1){
while(rs.next()){
System.out.println("創建動態生陳列test");
if(isHaveCosmeticcode.equals("yes")){
s.mergeCells(13+mycol, 2, 15+mycol, 2);
s.addCell(new Label(column+mycol,3,rs.getString(1),wcsB0));
mark = "yes";
}else{
mark = "no";
s.addCell(new Label(column+mycol-2,3,rs.getString(1),wcsB0));
}
column++;
}
System.out.println("column value==>"+column);
}else if(tag == 2){
while(rs.next()){
s.mergeCells(16+mycol, 0, 17+mycol, 2);
s.addCell(new Label(16+mycol,0,"Pkg + Version release within \n"+rs.getString(2) +" Days",wcsB0));
}
}else if(tag == 3){
while(rs.next()){
if(isHaveCosmeticcode.equals("yes")){
s.mergeCells(18+mycol, 0, 19+mycol, 2);
s.addCell(new Label(18+mycol,0,"Cosmetic release within \n"+rs.getString(2)+" Days",wcsB0));
}
}
}else if(tag == 4){
while (rs.next()) {
for (int j = 0; j < columnCount; j++) {
if (rs.getString(j + 1) == null) {
myvalue = "";
}else if(rsmd.getColumnTypeName(j+1).equals("smalldatetime")){ //转换excel数据类型
mydate = rs.getDate(j+1);
}else if (rsmd.getColumnTypeName(j+1).equals("numeric")){
testint = rs.getInt(j+1);
}else {
myvalue = rs.getString(j + 1);
}
if(k == 4){
if(j == 0){
s.setColumnView(0, 15); //設置列的寬度
s.addCell(new Label(0, 3, "PC #", wcsB0));
}else if(j ==1){
s.setColumnView(1, 15);
s.addCell(new Label(1, 3, "Revision", wcsB0));
}else if(j == 2){
s.addCell(new Label(2, 3, "CustP/O#", wcsB0));
}else if(j == 3){
s.addCell(new Label(3, 3, "Customer", wcsB0));
}else if(j == 4){
s.addCell(new Label(4 , 3, "Country", wcsB0));
}else if(j ==5){
s.addCell(new Label(5,3,"Mktg Share",wcsB0));
}else if(j ==6 ){
s.addCell(new Label(6,3,"Item#",wcsB0));
}else if(j == 7){
s.addCell(new Label(7,3,"MIX",wcsB0));
}else if(j > columnCount-mycol-1 && (j < columnCount)){
s.setColumnView(j - (columnCount-mycol)+8, 20);
s.addCell(new Label(j - (columnCount-mycol)+8, 3, ((String) skuList.get(j-(columnCount-mycol))).replace("\\n", "\n"), wcsB2));
}
else if(j == 8+mycol ){
s.addCell(new Label(8+mycol , 3, "Inn", wcsB0));
}else if(j == 9+mycol ){
s.addCell(new Label(9+mycol , 3, "Out", wcsB0));
}else if(j == 10+mycol ){
s.addCell(new Label(10+mycol , 3, "PC qty", wcsB0));
}else if(j == 11+mycol ){
s.addCell(new Label(11+mycol , 3, "Ctn", wcsB0));
}else if(j ==12+mycol ){
s.addCell(new Label(12+mycol, 3,"Rev. Details",wcsB0));
}else if(j == 13+mycol ){
s.addCell(new Label(13+mycol , 3, "Request Ship Date", wcsB0));
}else if(j == 14+mycol ){
s.addCell(new Label(14+mycol , 3, "Insp.Date", wcsB0));
}else if(j == 15+mycol ){
s.addCell(new Label(15+mycol , 3, "Customer Insp. Date", wcsB0));
}else if(j ==16+mycol){
s.addCell(new Label(16+mycol,3,"Packaging",wcsB0));
}else if(j ==17+mycol){
s.addCell(new Label(17+mycol,3,"Packaging Release Status",wcsB0));
}else if(j ==18+mycol){
if(isHaveCosmeticcode.equals("yes")){
s.addCell(new Label(18+mycol,3,"Cosmetic Version",wcsB0));
s.addCell(new Label(19+mycol,3,"Cosmetic Release Status",wcsB0));
}
}
else if(j > columnCount-2*mycol-1 && (j < columnCount-mycol)){
if(mark.equals("yes")){
s.setColumnView(column+mycol+va, 25);
// System.out.println("column is :"+column);
System.out.println("創建sku2列");
// System.out.println("mycol is :"+mycol);
s.addCell(new Label(column+mycol+va, 3, ((String) skuList.get(j-(columnCount-2*mycol))).replace("\\n", "\n"), wcsB2));
}else{
System.out.println("創建無comstccode列的sku");
s.setColumnView(column+mycol+va-2, 25);
s.addCell(new Label(column+mycol+va-2,3,((String)skuList.get(j-(columnCount-2*mycol))).replace("\\n", "\n"),wcsB2));
}
++va;
}
}
if(k >= 4){ //j = j rs =j+1 value
if(j == 33){
s.addCell(new Label(0, k,myvalue, wcsB));
}else if(j == 10){
s.addCell(new Label(1, k, myvalue, wcsB));
}else if(j == 19){
s.addCell(new Label(2, k, myvalue, wcsB));
}else if(j == 20){
s.addCell(new Label(3, k,myvalue, wcsB));
}else if(j == 7){
s.addCell(new Label(4 , k, myvalue, wcsB));
}else if(j == 9){
s.addCell(new Label(5,k,myvalue,wcsB));
}else if(j == 21){
s.addCell(new Label(8+mycol , k, String.valueOf(testint), wcsB));
}else if(j == 22){
s.addCell(new Label(9+mycol , k, String.valueOf(testint), wcsB));
}else if(j == 31){
s.addCell(new Label(6,k,myvalue,wcsB));
}else if(j == 34){
s.addCell(new Label(7,k,myvalue,wcsB));
}else if(j == 35){
s.addCell(new Label(10+mycol , k, String.valueOf(testint), wcsB));
}else if(j == 8){
s.addCell(new Label(11+mycol , k, myvalue, wcsB));
}else if(j == 23){
s.addCell(new Label(12+mycol , k, myvalue, wcsB));
}else if(j == 24){
s.setColumnView(16, 15);
s.addCell(new jxl.write.DateTime(13+mycol , k, mydate, wcfDF));
}else if (j ==4){
s.setColumnView(17,15);
s.addCell(new jxl.write.DateTime(14+mycol,k,mydate,wcfDF));
}else if (j ==26){
s.setColumnView(18, 15);
s.addCell(new jxl.write.DateTime(15+mycol,k,mydate,wcfDF));
}else if(j == 0){
s.addCell(new Label(16+mycol,k,myvalue,wcsB));
}else if(j == 2){
s.addCell(new Label(17+mycol,k,myvalue,wcsB));
}else if(j == 1 ){
s.addCell(new Label(18+mycol,k,myvalue,wcsB));
}else if(j == 3){//如果，Cosmetic Version栏位对应为空，则Cosmetic Release Status不予计算
s.addCell(new Label(19+mycol,k,myvalue,wcsB));
}
/*
* 动态生成列 0(='_'=)0
* */
else if(j>36 && j<36+mycol+1){
if(mark.equals("yes")){
s.addCell(new Label(j-columnCount+2*mycol+2*(column-20)+20+mycol,k,String.valueOf(rs.getInt(j+1)),wcsB));
}else{
s.addCell(new Label(j-columnCount+2*mycol+2*(column-20)+20+mycol-2,k,String.valueOf(rs.getInt(j+1)),wcsB));
}
}else if(j> 36+mycol && j< 36+2*mycol+1){
s.addCell(new Label(j-columnCount+mycol+(column-20)+8,k,String.valueOf(rs.getInt(j+1)),wcsB));
}else if (j>36+2*mycol && j<columnCount){
if(mark.equals("yes")){
s.addCell(new Label(j-columnCount+(column-20)+16+4+mycol,k,myvalue.equals("")||myvalue.equals("null")?" ":myvalue,wcsB));
}else{
s.addCell(new Label(j-columnCount+(column-20)+16+4+mycol-2,k,myvalue.equals("")||myvalue.equals("null")?" ":myvalue,wcsB));
}
}
}
}
k++;
}
}
tag++;
flag_label = stmt.getMoreResults();
continue;
}
label = up_label ||flag_label;
}
//生成表头
if(mark.equals("yes")){
s.mergeCells(mycol+column,0,mycol+column+mycol-1,2);
s.addCell(new Label((mycol+column),0,"CONTENT BREAKDOWN",wchB1));
}else{
s.mergeCells(mycol+column-2,0,mycol+column+mycol-1-2,2);
s.addCell(new Label((mycol+column)-2,0,"CONTENT BREAKDOWN",wchB1));
}
w.write();
String myFmt2=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
System.out.println("excel創建完成，結束時間為："+myFmt2);
w.close();
} catch (Exception e) {
System.out.println("創建excel失敗，錯誤信息:"+e.getMessage());
e.printStackTrace();
throw new ServletException("Exception in Excel Sample Servlet", e);
} finally {
//释放资源
SimpleServlet.axaptaCP.free(con);
if (out != null)
out.close();
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
报表开发（测试版本一）
最新推荐文章于 2019-09-04 18:14:24 发布
扫一扫
5703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
