import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
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.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.hssf.util.Region;
public class test{
public static void main(String[] args) {
// TODO Auto-generated method stub
FileOutputStream fileOut = null;
File file = new File("c:/1.xls");
String[] header = { "服务器名", "IP地址", "库cache命中率_最大值", "库cache命中率_平均值",
"连接数_最大值", "连接数_平均值", "游标数_最大值", "游标数_平均值", "session数_最大值",
"session数_平均值", "事务数_最大值", "事务数_平均值", "数据库锁数_最大值", "数据库锁数_平均值",
"缓冲池命中数_最大值", "缓冲池命中数_平均值", "表空间_使用率_最大值", "表空间_使用率_平均值",
"表空间_已用空间_最大值", "表空间_已用空间_平均值", "表空间_剩余率_最大值", "表空间_剩余率_平均值",
"表空间_剩余空间_最大值", "表空间_剩余空间_平均值", "表空间_总容量_最大值", "表空间_总容量_平均值",
"连接状态" };
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFFont fontinfo = wb.createFont();
fontinfo.setFontHeightInPoints((short) 8); // 字体大小
HSSFFont fonthead = wb.createFont();
fonthead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
HSSFCellStyle cellStylename = wb.createCellStyle();// 表名样式
cellStylename.setFont(fonthead);
HSSFCellStyle cellStyleinfo = wb.createCellStyle();// 表信息样式
cellStyleinfo.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 对齐
cellStyleinfo.setFont(fontinfo);
HSSFCellStyle cellStylehead = wb.createCellStyle();// 表头样式
cellStylehead.setFont(fonthead);
cellStylehead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStylehead.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 边框
cellStylehead.setBottomBorderColor(HSSFColor.BLACK.index);
cellStylehead.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStylehead.setLeftBorderColor(HSSFColor.BLACK.index);
cellStylehead.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStylehead.setRightBorderColor(HSSFColor.BLACK.index);
cellStylehead.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStylehead.setTopBorderColor(HSSFColor.BLACK.index);
cellStylehead.setFillForegroundColor(HSSFColor.ORANGE.index);
cellStylehead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFCellStyle cellStyle = wb.createCellStyle();// 数据单元样式
cellStyle.setWrapText(true);// 自动换行
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
int col = header.length;
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStylename);
cell.setCellValue("oracle");// 表名
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1));
cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyleinfo);
cell.setCellValue("oracle"); // 信息
sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) col));
int rows_max = 0;
for (int i = 0; i < header.length; i++) {
String h = header[i];
if (h.split("_").length > rows_max) {
rows_max = h.split("_").length;
}
}
Map map = new HashMap();
for (int k = 0; k < rows_max; k++) {
row = sheet.createRow((short) k + 1);
if (k == 0) {
cell = row.createCell((short) (0));
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStylehead);
cell.setCellValue("序号");
sheet.addMergedRegion(new Region(k + 1, (short) 0, k
+ rows_max, (short) 0));
}
for (int i = 0; i < header.length; i++) {
String headerTemp = header[i];
String[] s = headerTemp.split("_");
String sk = "";
int num = i + 1;
if (s.length == 1) {
cell = row.createCell((short) (num));
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStylehead);
sheet.addMergedRegion(new Region(1, (short) (num),
rows_max, (short) (num)));
sk = headerTemp;
cell.setCellValue(sk);
} else {
System.out.println(sk);
cell = row.createCell((short) (num));
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStylehead);
int cols = 0;
if (map.containsKey(headerTemp)) {
continue;
}
for (int d = 0; d <= k; d++) {
if (d != k) {
sk += s[d] + "_";
} else{
sk += s[d];
}
}
if(map.containsKey(sk)){
continue;
}
for (int j = 0; j < header.length; j++) {
if (header[j].indexOf(sk) != -1) {
cols++;
}
}
cell.setCellValue(s[k]);
sheet
.addMergedRegion(new Region(k + 1,
(short) num, k + 1,
(short) (num + cols-1)));
if (sk.equals(headerTemp)) {
sheet.addMergedRegion(new Region(k + 1,
(short) num, k + 1 + rows_max
- s.length, (short) num));
}
}
if (s.length > k) {
if (!map.containsKey(sk)) {
String key = "";
if (k > 0) {
key = sk;
} else {
key = s[k];
}
map.put(key, null);
}
}
}
}
fileOut = new FileOutputStream(file);
// modify by xywang(2007-11-19) end
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
}
}