package com.dj.action.uccb.test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.struts2.convention.annotation.Action;
import com.dj.action.uccb.UCCBAction;
import com.dj.pageModel.base.Json;
import com.dj.pageModel.uccb.cooperator.Personinfo;
@Action(value = "Db2Excel", results = {})
public class Db2ExcelAction extends UCCBAction<Personinfo> {
/**
*
*/
private static final long serialVersionUID = 1L;
@PersistenceContext
private EntityManager entityManager;
public void getTables() throws Exception{
Json j = new Json();
j.setSuccess(true);
String Msg = "";
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");//设置日期格式
String nowDate = df.format(new Date());
String time = df1.format(new Date());
String filePath = "D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\已分配\\乌商项目管理平台表结构汇总"+nowDate+".xls";
Query query = entityManager
.createNativeQuery("select name,remarks from sysibm.systables where type='T' and creator = 'UBMPOA' AND NAME LIKE 'T_UBMP\\_%\\_%' escape '\\' order by name asc");
List result = query.getResultList();
FileOutputStream os = null;
WritableWorkbook wwb = null;
File file = new File(filePath);
if (!file.isFile())// 如果指定文件不存在,则新建该文件
file.createNewFile();
os = new FileOutputStream(file);// 创建一个输出流
wwb = Workbook.createWorkbook(os);
WritableSheet sheet1 = wwb.createSheet("目录",0);
WritableFont font1 = new WritableFont(WritableFont.createFont("Microsoft Sans Serif"),9);
WritableCellFormat cellcenter1 = new WritableCellFormat(font1);
cellcenter1.setAlignment(Alignment.CENTRE);
cellcenter1.setBackground(jxl.format.Colour.PALE_BLUE);
cellcenter1.setBorder(Border.ALL,BorderLineStyle.THIN);
sheet1.mergeCells(2, 2, 3, 2);
Label title = new Label(2, 2, "共计"+result.size()+"张表,更新于"+time,cellcenter1);
sheet1.addCell(title);
Msg = Msg +"共计"+result.size()+"张表,更新于"+time;
// WritableSheet sheet2 = wwb.createSheet("汇总表",1);
for (int index = 0; index < result.size(); index++) {
String tableName = ((Object[]) result.get(index))[0].toString();
String tableNamedesc = (String) ((Object[]) result.get(index))[1];
tableNamedesc = tableNamedesc == null ? tableName: tableNamedesc;
// String tableName = "T_UBMP_PERSONINFO";
// String tableNamedesc = "人员信息表";
// System.out.println(tableName);
String sql = "select colno, name,remarks,typename,length,nulls from sysibm.syscolumns where tbcreator = 'UBMPOA' and tbname = '"+tableName + "' order by colno asc";
Query queryTable = entityManager.createNativeQuery(sql);
List tableDesc = queryTable.getResultList();
WritableSheet sheet = wwb.createSheet(tableNamedesc, index+1);// 创建一个工作页,第一个参数的页名,第二个参数表示该工作页在excel中处于哪一页
sheet.setColumnView(0, 10);
sheet.setColumnView(1, 30);
sheet.setColumnView(2, 30);
sheet.setColumnView(3, 18);
sheet.setColumnView(4, 10);
sheet.setColumnView(5, 60);
//设置文字居中对齐方式;
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
cellFormat1.setAlignment(Alignment.LEFT);
cellFormat1.setBackground((jxl.format.Colour.LIGHT_TURQUOISE));
cellFormat1.setBorder(Border.ALL,BorderLineStyle.THIN);
WritableCellFormat cellcenter = new WritableCellFormat(font1);
cellcenter.setAlignment(Alignment.CENTRE);
cellcenter.setBackground(jxl.format.Colour.LIGHT_TURQUOISE);
cellcenter.setBorder(Border.ALL,BorderLineStyle.THIN);
WritableFont font2 = new WritableFont(WritableFont.createFont("宋体"),9);
WritableCellFormat celltitle = new WritableCellFormat(font1);
celltitle.setAlignment(Alignment.CENTRE);
celltitle.setBackground(jxl.format.Colour.PALE_BLUE);
celltitle.setBorder(Border.ALL,BorderLineStyle.THIN);
Label labeltable = new Label(2, index+3, tableName,cellFormat1);
Label labeltabledesc = new Label(3, index+3, tableNamedesc,cellFormat1);
sheet1.addCell(labeltable);
sheet1.addCell(labeltabledesc);
sheet1.setColumnView(2, 30);
sheet1.setColumnView(3, 30);
sheet1.setColumnView(4, 10);
sheet1.setColumnView(5, 10);
sheet.mergeCells(0, 0, 5, 0);
System.out.println(tableName + "(" + tableNamedesc + ")");
Label label = new Label(0, 0, tableName + "(" + tableNamedesc + ")",celltitle);// 填充第一行第一个单元格的内容
sheet.addCell(label);
//Label labelreturn = new Label(6, 0, "返回",celltitle);// 填充第一行第一个单元格的内容
//sheet.addCell(labelreturn);
WritableHyperlink link = new WritableHyperlink(6, 0, 1, 0, "返回", sheet1, 4, index+3, 4, index+3);
sheet.addHyperlink(link);
Label label11 = new Label(0, 1, "序号",celltitle);
sheet.addCell(label11);
Label label12 = new Label(1, 1, "字段",celltitle);
sheet.addCell(label12);
Label label13 = new Label(2, 1, "字段描述",celltitle);
sheet.addCell(label13);
Label label14 = new Label(3, 1, "字段类型",celltitle);
sheet.addCell(label14);
Label label15 = new Label(4, 1, "是否必输",celltitle);
sheet.addCell(label15);
Label label16 = new Label(5, 1, "备注",celltitle);
sheet.addCell(label16);
for (int colindex = 0; colindex < tableDesc.size(); colindex++) {
String colnote = "";
String colno = String.valueOf(colindex+1);
String colname = ((Object[]) tableDesc.get(colindex))[1] == null ? "" : ((Object[]) tableDesc.get(colindex))[1].toString();
String coldesc = (String)((Object[]) tableDesc.get(colindex))[2];
if(coldesc != null && coldesc.length() > 1){
;
if(coldesc.trim().contains(":")){
int spaceFlag = coldesc.indexOf(":");
colnote = coldesc.substring(spaceFlag+1);
coldesc = coldesc.substring(0, spaceFlag);
}else if(coldesc.trim().contains("(")){
int spaceFlag = coldesc.indexOf("(");
colnote = coldesc.substring(spaceFlag+1);
coldesc = coldesc.substring(0, spaceFlag);
}else if(coldesc.trim().contains(" ")){
int spaceFlag = coldesc.indexOf(" ");
colnote = coldesc.substring(spaceFlag+1);
coldesc = coldesc.substring(0, spaceFlag);
}
}else{
colnote ="";
coldesc = "";
}
String coltype = ((Object[]) tableDesc.get(colindex))[3].toString()+"("+((Object[]) tableDesc.get(colindex))[4].toString()+")";
String colnullable = ((Object[]) tableDesc.get(colindex))[5].toString().equals("N") ? "M" :"O";
// 第一个参数表示列,第二个参数表示行
Label label0 = new Label(0, colindex+2, colno,cellcenter);
Label label1 = new Label(1, colindex+2, colname.toLowerCase(),cellFormat1);
Label label2 = new Label(2, colindex+2, coldesc.replace(":", ""),cellFormat1);
Label label3 = new Label(3, colindex+2, coltype,cellFormat1);
Label label4 = new Label(4, colindex+2, colnullable,cellcenter);
Label label5 = new Label(5, colindex+2, colnote,cellFormat1);
sheet.addCell(label0);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);
sheet.addCell(label5);
}
WritableHyperlink link1 = new WritableHyperlink(4, index+3, 4, index+3, "查看", sheet, 6, 0, 6, 0);
sheet1.addHyperlink(link1);
// Msg = Msg + tableName+ "("+tableNamedesc +")\n";
}
j.setMsg(Msg);
super.writeJson(j);
wwb.write();// 将内容写到excel文件中
os.flush();// 清空输出流
try {
if (wwb != null)
wwb.close();
if (os != null)
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public Personinfo getModel() {
// TODO Auto-generated method stub
return null;
}
}
转载于:https://my.oschina.net/u/2540218/blog/602462