/**
* <p>讀取Excel表格,拷貝、更新Excel工作薄 </p>
* <p>Description: 可以讀取Excel文件的內(nèi)容,更新Excel工作薄
* </p>
* <p>Copyright: Copyright (c) Corparation 2005</p>
* <p>程序開發(fā)環(huán)境為eclipse</p>
* @author Walker
* @version 1.0
*/
package cn.com.yitong.xls;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Vector;
import cn.com.yitong.ChartImg;
import cn.com.yitong.VireObj;
import cn.com.yitong.platform.log.YTLogger;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class XLSDemo
{
private static final int TITLE_LENGTH = 7;
private static final int SHEET_WIDTH = 32;
private static final int SHEET_HEIGHT = 116;
/**
* 創(chuàng)建Excel
*/
private void makeXls()
{
Workbook workbook = null;
try
{
// 構(gòu)建Workbook對象, 只讀Workbook對象
// 直接從本地文件創(chuàng)建Workbook, 從輸入流創(chuàng)建Workbook
InputStream ins = new FileInputStream("D:/Workspace/testproj/source.xls");
workbook = Workbook.getWorkbook(ins);
// 利用已經(jīng)創(chuàng)建的Excel工作薄創(chuàng)建新的可寫入的Excel工作薄
File outFile = new File("D:/Workspace/testproj/test.xls");
WritableWorkbook wwb = Workbook.createWorkbook(outFile, workbook);
// 讀取第一張工作表
WritableSheet dataSheet = wwb.getSheet(0);
// 設(shè)置凍結(jié)單元格
dataSheet.getSettings().setVerticalFreeze(7);
dataSheet.getSettings().setHorizontalFreeze(2);
// 測試模擬數(shù)據(jù)
Vector vecData = new Vector();
for(int i = 0; i < 50; i ++)
{
VireObj obj = new VireObj();
obj.setOrgNo("00" + i + "0");
obj.setOrgName("機構(gòu)" + (i + 1));
obj.setOpenAcc((int)(100 * Math.random()));
obj.setDestoryAcc((int)(10 * Math.random()));
obj.setTotalAcc((int)(500 * Math.random()));
obj.setMonthInCount((int)(500 * Math.random()));
obj.setMonthInMoney(500 * Math.random());
obj.setMonthOutCount((int)(500 * Math.random()));
obj.setMonthOutMoney(500 * Math.random());
vecData.add(obj);
}
// 插入數(shù)據(jù)
insertData(wwb, dataSheet, vecData);
// 插入模擬圖像數(shù)據(jù)
Vector vecImg = new Vector();
for(int i = 0; i < 3; i ++)
{
ChartImg img = new ChartImg();
img.setImgTitle("圖像" + (i + 1));
img.setImgName("D:/Workspace/testproj/images/barchart.png");
vecImg.add(img);
}
// 插入圖表
insertImgsheet(wwb, vecImg);
//寫入Excel對象
wwb.write();
wwb.close();
} catch (Exception e)
{
YTLogger.logDebug(e);
} finally
{
// 操作完成時，關(guān)閉對象，釋放占用的內(nèi)存空間
workbook.close();
}
}
/**
* 插入數(shù)據(jù)
* @param wwb WritableWorkbook : 工作簿
* @param dataSheet WritableSheet : 工作表
* @throws RowsExceededException
* @throws WriteException
*/
private void insertData(WritableWorkbook wwb, WritableSheet dataSheet, Vector vecData) throws RowsExceededException, WriteException
{
// 獲得標(biāo)題單元格對象
modiStrCell(dataSheet, 2, 0, "工商銀行江蘇省分行 個人網(wǎng)上銀行業(yè)務(wù)種類/開銷戶明細(xì)報表（2005-12）", null);
// 修改數(shù)據(jù)單元格數(shù)據(jù)
for(int i = 0; i < vecData.size(); i ++)
{
VireObj obj = (VireObj)vecData.get(i);
modiStrCell(dataSheet, 0, TITLE_LENGTH + i, obj.getOrgNo(), null);
modiStrCell(dataSheet, 1, TITLE_LENGTH + i, obj.getOrgName(), null);
modiNumCell(dataSheet, 2, TITLE_LENGTH + i, obj.getOpenAcc(), null);
modiNumCell(dataSheet, 3, TITLE_LENGTH + i, obj.getDestoryAcc(), null);
modiNumCell(dataSheet, 4, TITLE_LENGTH + i, obj.getTotalAcc(), null);
modiNumCell(dataSheet, 5, TITLE_LENGTH + i, obj.getMonthInCount(), null);
modiNumCell(dataSheet, 6, TITLE_LENGTH + i, obj.getTotalInMoney(), null);
modiNumCell(dataSheet, 7, TITLE_LENGTH + i, obj.getMonthOutCount(), null);
modiNumCell(dataSheet, 8, TITLE_LENGTH + i, obj.getMonthOutMoney(), null);
}
// 刪除空行
for (int j = vecData.size() + TITLE_LENGTH; j < SHEET_HEIGHT; j++)
{
dataSheet.removeRow(vecData.size() + TITLE_LENGTH);
}
// 插入公式
for(int i = 2; i < SHEET_WIDTH; i ++)
{
modiFormulaCell(dataSheet, i, vecData.size() + TITLE_LENGTH, 8, vecData.size() + TITLE_LENGTH, null);
}
}
/**
* 修改字符單元格的值
* @param dataSheet WritableSheet : 工作表
* @param col int : 列
* @param row int : 行
* @param str String : 字符
* @param format CellFormat : 單元格的樣式
* @throws RowsExceededException
* @throws WriteException
*/
private void modiStrCell(WritableSheet dataSheet, int col, int row, String str, CellFormat format) throws RowsExceededException, WriteException
{
// 獲得單元格對象
WritableCell cell = dataSheet.getWritableCell(col, row);
// 判斷單元格的類型, 做出相應(yīng)的轉(zhuǎn)化
if (cell.getType() == CellType.EMPTY)
{
Label lbl = new Label(col, row, str);
if(null != format)
{
lbl.setCellFormat(format);
} else
{
lbl.setCellFormat(cell.getCellFormat());
}
dataSheet.addCell(lbl);
} else if (cell.getType() == CellType.LABEL)
{
Label lbl = (Label)cell;
lbl.setString(str);
} else if (cell.getType() == CellType.NUMBER)
{
// 數(shù)字單元格修改
Number n1 = (Number)cell;
n1.setValue(42.05);
}
}
/**
* 修改數(shù)字單元格的值
* @param dataSheet WritableSheet : 工作表
* @param col int : 列
* @param row int : 行
* @param num double : 數(shù)值
* @param format CellFormat : 單元格的樣式
* @throws RowsExceededException
* @throws WriteException
*/
private void modiNumCell(WritableSheet dataSheet, int col, int row, double num, CellFormat format) throws RowsExceededException, WriteException
{
// 獲得單元格對象
WritableCell cell = dataSheet.getWritableCell(col, row);
// 判斷單元格的類型, 做出相應(yīng)的轉(zhuǎn)化
if (cell.getType() == CellType.EMPTY)
{
Number lbl = new Number(col, row, num);
if(null != format)
{
lbl.setCellFormat(format);
} else
{
lbl.setCellFormat(cell.getCellFormat());
}
dataSheet.addCell(lbl);
} else if (cell.getType() == CellType.NUMBER)
{
// 數(shù)字單元格修改
Number lbl = (Number)cell;
lbl.setValue(num);
} else if (cell.getType() == CellType.LABEL)
{
Label lbl = (Label)cell;
lbl.setString(String.valueOf(num));
}
}
/**
* 修改公式單元格的值
* @param dataSheet WritableSheet : 工作表
* @param col int : 列
* @param row int : 行
* @param startPos int : 開始位置
* @param endPos int : 結(jié)束位置
* @param format
* @throws RowsExceededException
* @throws WriteException
*/
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);
if (cell.getType() == CellType.EMPTY)
{
// 公式單元格
Formula lbl = new Formula(col, row, f);
if(null != format)
{
lbl.setCellFormat(format);
} else
{
lbl.setCellFormat(cell.getCellFormat());
}
dataSheet.addCell(lbl);
} else if (cell.getType() == CellType.STRING_FORMULA)
{
YTLogger.logWarn("Formula modify not supported!");
}
}
/**
* 得到公式
* @param col int : 列
* @param row int : 行
* @param startPos int : 開始位置
* @param endPos int : 結(jié)束位置
* @return String
* @throws RowsExceededException
* @throws WriteException
*/
private String getFormula(int col, int row, int startPos, int endPos)
throws RowsExceededException, WriteException
{
char base = 'A';
char c1 = base;
StringBuffer formula = new StringBuffer(128);
// 組裝公式
formula.append("SUM(");
if (col <= 25)
{
c1 = (char) (col % 26 + base);
formula.append(c1).append(startPos).append(":")
.append(c1).append(endPos).append(")");
} else if (col > 25)
{
char c2 = (char) ((col - 26) / 26 + base);
c1 = (char) ((col - 26) % 26 + base);
formula.append(c2).append(c1).append(startPos).append(":")
.append(c2).append(c1).append(endPos).append(")");
}
return formula.toString();
}
/**
* 插入圖表工作表
* @param wwb WritableWorkbook : 工作簿
* @param vecImg Vector : 圖像鏈表
* @throws RowsExceededException
* @throws WriteException
*/
private void insertImgsheet(WritableWorkbook wwb, Vector vecImg)
throws RowsExceededException, WriteException
{
// 插入圖像
WritableSheet imgSheet;
if((wwb.getSheets()).length < 2)
{
imgSheet = wwb.createSheet("圖表", 1);
} else
{
imgSheet = wwb.getSheet(1);
}
for (int i = 0; i < vecImg.size(); i++)
{
ChartImg chart = (ChartImg) vecImg.get(i);
// 插入圖像標(biāo)題
Label lbl = new Label(0, 2 + 20 * i, chart.getImgTitle());
WritableFont font = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.DARK_BLUE2);
WritableCellFormat background = new WritableCellFormat(font);
background.setWrap(true);
background.setBackground(Colour.GRAY_25);
imgSheet.mergeCells(0, 2 + 20 * i, 9, 2 + 20 * i);
lbl.setCellFormat(background);
imgSheet.addCell(lbl);
// 插入圖像單元格
insertImgCell(imgSheet, 2, 4 + 20 * i, 8, 15, chart.getImgName());
}
}
/**
* 插入圖像到單元格（圖像格式只支持png）
* @param dataSheet WritableSheet : 工作表
* @param col int : 列
* @param row int : 行
* @param width int : 寬
* @param height int : 高
* @param imgName String : 圖像的全路徑
* @throws RowsExceededException
* @throws WriteException
*/
private void insertImgCell(WritableSheet dataSheet, int col, int row, int width,
int height, String imgName) throws RowsExceededException, WriteException
{
File imgFile = new File(imgName);
WritableImage img = new WritableImage(col, row, width, height, imgFile);
dataSheet.addImage(img);
}
/**
* 測試
* @param args
*/
public static void main(String[] args)
{
XLSDemo demo = new XLSDemo();
demo.makeXls();
}
}
柳德才
13691193654
18942949207
QQ：422157370
liudecai_zan@126.com湖北-武漢-江夏-廟山