package nc.ui.fh.des.excel;
import java.io.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import java.util.Map.Entry;
import javax.swing.JFileChooser;
import javax.swing.JTable;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.bill.BillItem;
import nc.ui.pub.report.ReportBaseClass;
import jxl.*;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
/**
* 报表导出Excel的工具类
* @author 麻杰<br>
* @创建时间:2011-11-16 下午06:08:23
*/
public class ExportExcelReport {
protected static javax.swing.JFileChooser m_chooser = null;
protected static ReportBaseClass reportUIBase;
/**
* @author 麻杰<br>
* 日期:2011-11-17
* @param tp 父组件
* @param sheetName Excel页签名称
* @param istotal 是否合计
*/
public static void exportExcel(ReportBaseClass tp ,String sheetName,boolean istotal){
reportUIBase = tp;
String filePath = null;
if (getChooser().showSaveDialog(tp) == javax.swing.JFileChooser.CANCEL_OPTION) {
return;
}
filePath = getChooser().getSelectedFile().toString();
if (filePath == null) {
MessageDialog.showHintDlg(tp, "警告", "请输入文件名保存!");
return;
}
if (filePath.indexOf(".xls") < 0) {
filePath = filePath + ".xls";
}
boolean isSucceed = writeJxlByTableModel(filePath,sheetName,istotal);
if(isSucceed){
MessageDialog.showHintDlg(tp, "提示", "导出完成");
}else{
MessageDialog.showHintDlg(tp, "提示", "导出失败");
}
}
/**
* 生成Excel文件
* @author 麻杰<br>
* 日期:2011-11-17
* @param filePatch
* @param sheetName
* @param istotal 是否合计
* @return
*/
public static boolean writeJxlByTableModel(String filePath,String sheetName,boolean istotal) {
JTable table = reportUIBase.getBillTable();
if (table == null || table.getRowCount() <= 0)
return false;
WritableWorkbook writableWorkbook = null;
OutputStream os = null;
try {
os = new FileOutputStream(filePath);
// 创建可写簿
writableWorkbook = Workbook.createWorkbook(os);
// 创建工作表
WritableSheet ws = writableWorkbook.createSheet(sheetName, 0);
// 创建一个内容 第一个整数为 列,第二个整数位 行
Label label = null;
int rows = table.getRowCount();
int cols = table.getColumnCount();
for (int row = 0; row < rows + 1; row++) {
for (int col = 0; col < cols ; col++) {
if(row == 0){
String headerName = table.getTableHeader().getColumnModel().getColumn(col).getHeaderValue() == null ? "" :
table.getTableHeader().getColumnModel().getColumn(col).getHeaderValue().toString();
label = new Label(col,row,headerName,getHeaderFormat());
ws.addCell(label);
ws.setColumnView(col, headerName == null ? 10 : (headerName.length() > 2 ? headerName.length()*3 : headerName.length()*6));
}
label = new Label(col,row+1,table.getValueAt(row, col) == null ? "" : table.getValueAt(row, col).toString());
if(label.getContents() != null){
if(label.getContents().equals("false")){
label.setString("N");
}else if(label.getContents().equals("true")){
label.setString("Y");
}
}
ws.addCell(label);
}
}
if(istotal){
//添加合计行
Map<Integer,String> map = getTotalMap();
if(map != null && map.size()>0){
Set<Entry<Integer,String>> set = map.entrySet();
Iterator it = set.iterator();
while(it.hasNext()){
Entry<Integer,String> entry = (Entry<Integer, String>) it.next();
label = new Label(entry.getKey(),rows+1,entry.getValue());
ws.addCell(label);
}
}
}
writableWorkbook.write();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
if(writableWorkbook != null){
try {
writableWorkbook.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
if(os != null){
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return true;
}
/**
* 获得文件选择器
* @author 麻杰<br>
* 日期:2011-11-17
* @return
*/
private static javax.swing.JFileChooser getChooser() {
if (m_chooser == null) {
m_chooser = new JFileChooser();
m_chooser.setDialogType(JFileChooser.SAVE_DIALOG);
}
return m_chooser;
}
/**
* 获得表头行样式
* @author 麻杰<br>
* 日期:2011-11-16
* @throws WriteException
*/
private static WritableCellFormat getHeaderFormat() throws WriteException{
WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
WritableCellFormat format = new WritableCellFormat(font);
format.setWrap(false);
format.setAlignment(Alignment.CENTRE);//水平方向剧中
format.setVerticalAlignment(VerticalAlignment.CENTRE);
return format;
}
/**
* 获得合计封装Map
* @author 麻杰<br>
* 日期:2011-11-18
* @return
*/
private static Map<Integer,String> getTotalMap(){
Vector vector = (Vector)reportUIBase.getTotalTableModel().getDataVector().get(0);
Object total = null;
Map<Integer,String> map = new HashMap<Integer,String>();
for(int i=0;i<vector.size();i++){
total = vector.get(i);
if(total != null){
int result = getOrder(i);
if(result != -1){
map.put(result, total.toString());
}
}
}
return map;
}
/**
* 根据合计值的位置获得其在UITable中的位置
* @author 麻杰<br>
* 日期:2011-11-18
* @param num
* @return
*/
private static Integer getOrder(int num){
int result = 0;
BillItem[] billItem = reportUIBase.getBodyItems();
if(!billItem[num].isShow()){
return -1;
}
for(int i=0;i<num;i++){
if(billItem[i].isShow()){
result++;
}
}
return result;
}
}