[RockWell FTPC]随笔_FTPC AT表明细导出

package com.rockwell.test;

import java.io.FileOutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
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.ss.usermodel.Hyperlink;
import com.rockwell.mes.commons.base.ifc.services.PCContext;

import jxl.CellType;

public class GenerateATTable
{
	static HSSFWorkbook workbook  ;
	static String mainSheetName ="AT表";
	static HSSFCellStyle borderStyle;
	public void generate()
	{
		System.out.println("==========start=============");

		workbook = new HSSFWorkbook();
		HSSFSheet hssfSheet = workbook.createSheet(mainSheetName);

		borderStyle = workbook.createCellStyle();
		borderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		borderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		borderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		borderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		
		HSSFRow row = hssfSheet.createRow(0);
		row.createCell(0).setCellValue("序号");
		row.createCell(1).setCellValue("链接");
		row.createCell(2).setCellValue("AT_NAME");
		row.createCell(3).setCellValue("DESCRIPTION");
		row.createCell(4).setCellValue("CATEGORY");
		row.createCell(5).setCellValue("备注");


		setColumnWith(hssfSheet,0, 5);
		setColumnWith(hssfSheet,1, 10);
		setColumnWith(hssfSheet,2, 40);
		setColumnWith(hssfSheet,3, 40);
		setColumnWith(hssfSheet,4, 10);
		setColumnWith(hssfSheet,5, 40);


		String sql = "SELECT AT_KEY, AT_NAME ,DESCRIPTION,CATEGORY  FROM APP_TABLE at2 ORDER BY AT_NAME ";

		List<String[]>  tableList  =PCContext.getFunctions().getArrayDataFromActive(sql);
		for(int i=0;i<tableList.size();i++)
		{
			HSSFRow row2  = hssfSheet.createRow(i+1);
			row2.createCell(0).setCellValue(i+1);
			HSSFCell cell = row2.createCell(1);
			cell.setCellValue("明细");
			row2.createCell(2).setCellValue(tableList.get(i)[1]);
			row2.createCell(3).setCellValue(tableList.get(i)[2]);
			row2.createCell(4).setCellValue(tableList.get(i)[3]);
			row2.createCell(5).setCellValue("");

			Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);  
			hyperlink.setAddress("#"+tableList.get(i)[1]+"!A1");  
			cell.setHyperlink(hyperlink); 
			genenrateTableDetail(tableList.get(i)[1],tableList.get(i)[0]);

		}
		setCellStyle(hssfSheet);
		FileOutputStream out = null;
		try {
			out = new FileOutputStream("F:/MES_AT表清单.xls");
			workbook.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();

		}

		System.out.println("==========end=============");
	}

	public void genenrateTableDetail(String tableName,String atKey)
	{
		HSSFSheet hssfSheet = workbook.createSheet(tableName);
		HSSFRow row0 = hssfSheet.createRow(0);
		HSSFCell cell = row0.createCell(0);
		cell.setCellValue("返回");
		Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);  
		hyperlink.setAddress("#"+mainSheetName+"!A1");  
		cell.setHyperlink(hyperlink); 
		
		HSSFRow row = hssfSheet.createRow(1);
		row.createCell(0).setCellValue("序号");
		row.createCell(1).setCellValue("ATC_NAME");
		row.createCell(2).setCellValue("DESCRIPTION");
		row.createCell(3).setCellValue("ATC_DATATYPE");
		row.createCell(4).setCellValue("IS_NULLABLE");
		row.createCell(5).setCellValue("TEXT_LENGTH");
		row.createCell(6).setCellValue("备注");	

		setColumnWith(hssfSheet,0, 5);
		setColumnWith(hssfSheet,1, 30);
		setColumnWith(hssfSheet,2, 30);
		setColumnWith(hssfSheet,3, 10);
		setColumnWith(hssfSheet,4, 10);
		setColumnWith(hssfSheet,5, 10);
		setColumnWith(hssfSheet,6, 40);

		String sql = " SELECT ATC_NAME ,DESCRIPTION ,ATC_DATATYPE ,IS_NULLABLE ,TEXT_LENGTH FROM APP_TABLE_COLUMN atc  WHERE AT_KEY  = "+atKey+" ORDER BY ATC_NAME ";
		List<String[]>  tableList  =PCContext.getFunctions().getArrayDataFromActive(sql);
		for(int i=0;i<tableList.size();i++)
		{
			HSSFRow row2  = hssfSheet.createRow(i+2);
			row2.createCell(0).setCellValue(i+1);
			row2.createCell(1).setCellValue(tableList.get(i)[0]);
			row2.createCell(2).setCellValue(tableList.get(i)[1]);
			row2.createCell(3).setCellValue(tableList.get(i)[2]);
			row2.createCell(4).setCellValue(tableList.get(i)[3]);
			row2.createCell(5).setCellValue(tableList.get(i)[4]);
			row2.createCell(6).setCellValue("");
		}
		setCellStyle(hssfSheet);
	}

	public void setCellStyle(HSSFSheet hssfSheet)
	{
		try
		{
			int rowNum = hssfSheet.getLastRowNum();
			int colNum = hssfSheet.getRow(1).getLastCellNum();
			for(int i = 0;i<=rowNum;i++)
			{
				for(int j=0;j<colNum;j++)
				{
					HSSFCell cell = hssfSheet.getRow(i).getCell(j);
					if(cell!=null)
					{
						cell.setCellStyle(borderStyle);
					}
				}

			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public void setColumnWith(HSSFSheet hssfSheet,int columnIndex,int width)
	{
		hssfSheet.setColumnWidth(columnIndex,  (int)((width + 0.72) * 256));
	}
}