package com.together.printbean;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.imageio.ImageIO;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import com.together.dbbean.DBOptBean;
public class PrintBean {
public void printInfo(String str1, String str2, String tbl,boolean bol1,boolean bol2)
throws Exception {
// 分割工作表
// HSSFSheet.createSplitPane(int colSplit, int rowSplit, int
// leftmostColumn, int topRow);
// 固定某行不滚动
// HSSFSheet.createFreezePane(int colSplit, int rowSplit, int
// leftmostColumn, int topRow);
FileInputStream is = new FileInputStream("F://sss.xls");
// POIFSFileSystem fs = new POIFSFileSystem(is);
//
// HSSFWorkbook wb = new HSSFWorkbook(fs);
//
// // wb.cloneSheet(0); //复制工作表
// is.close();
// HSSFSheet sheet = wb.getSheetAt(0);
// int len = sheet.getPhysicalNumberOfRows();
// HSSFRow row = null;
// HSSFCell cell = null;
// int rlen = 0;
// for(int i=0;i<len;i++){
// row = sheet.getRow((short)i);
// rlen = row.getLastCellNum();
// for(int j=0;j<rlen;j++){
// cell = row.createCell((short)j);
// cell.setCellValue("");
// }
//
// }
String str = str1;
FileOutputStream fileout = new FileOutputStream("F://test.xls");
// 创建xls对象
HSSFWorkbook wb = new HSSFWorkbook();
// wb.setSheetName(0,"",wb.ENCODING_UTF_16);
// 创建工作表
HSSFSheet sheet = wb.createSheet();
// 设置工作表为只读不可修改
sheet.setProtect(true);
sheet.setPrintGridlines(true);
String strv = str2;
// 中文转码
strv = new String(strv.getBytes("ISO-8859-1"), "UTF-8");
String ssv = strv.substring(0, strv.length() - 1);
String[] skv = ssv.split(",");
int lenv = skv.length;
int iv = 0;
// 虚线HSSFCellStyle.BORDER_DOTTED
// 实线HSSFCellStyle.BORDER_THIN
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setLocked(true);
// 设置自动换行
//style.setWrapText(true);
// 创建对象
HSSFRow rowv = sheet.createRow(0);
// 样式对象
HSSFCellStyle cs = wb.createCellStyle();
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);//
cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);//
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//
// 创建字体
HSSFFont ff = wb.createFont();
// ff.setColor(HSSFFont.COLOR_RED);// 字体颜色
ff.setFontHeightInPoints((short) 11);// 字体大小
// ff.setFontName("");// 设置字体，没用
ff.setBoldweight((short) 700);
ff.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
cs.setFont(ff);//放入样式中
//循环标题
for (int k = 0; k < lenv; k++) {
HSSFCell cell0 = rowv.createCell((short) k);
// cell0.setCellStyle(style);
cell0.setCellStyle(cs);
cell0.setEncoding(cell0.ENCODING_UTF_16);//设置编码格式
cell0.setCellValue(skv[k]);
}
String ss = str.substring(0, str.length() - 1);
String tb = tbl;
String[] sk = ss.split(",");
int len = sk.length;
int i = 1;
// sql 语句
String sql = "select " + ss.toUpperCase() + " from " + tb;
// 数据库对象
DBOptBean dbb = new DBOptBean();
Connection conn = dbb.GetConn();
// 设置游标可恢复
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);
int o = 0;
int k = 0;
int[] cc = { 0, 0 };
int[] dd = { 0, 0 };
int xx = 0;
int length = 1;
// 得到列最长长度
while (rs.next()) {
for (int er = 1; er <= len; er++) {
xx = er - 1;
dd[xx] = rs.getObject(er).toString().getBytes().length;
if (cc[xx] < dd[xx]) {
cc[xx] = dd[xx];
}
}
}
rs.beforeFirst();//游标恢复
while (rs.next()) {
length++;
HSSFRow row = sheet.createRow(i);
row.setHeight((short) 250);
for (int j = 1; j <= len; j++) {
o = j - 1;
sheet.setColumnWidth((short) o, (short) (280 * cc[o]));
HSSFCell cell = row.createCell((short) o);
// 加入数据
cell.setCellStyle(style);
cell.setEncoding(cell.ENCODING_UTF_16);
cell.setCellValue(rs.getObject(j).toString());
o++;
}
i++;
k++;
}
if(bol1){
ViewBean vb = new ViewBean();
vb.viewInfo();
if(bol2){
BufferedImage bufferImg = null;
try {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
bufferImg = ImageIO.read(new File("F:/hoho.png"));
ImageIO.write(bufferImg, "png", byteArrayOut);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//HSSFClientAnchor 控制图片位置 参数详解
//(short)no.1 参数1为控制图片离左墙的距离
//(short)no.2 参数2为控制图片离房顶的距离
//(short)no.3 参数1为控制图片的宽度width
//(short)no.4 参数2为控制图片的高度height
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,
(short) 0, length, (short) 5, 30);
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
} catch (IOException io) {
io.printStackTrace();
System.out.println("io erorr : " + io.getMessage());
}
}
}
wb.write(fileout);
// 关闭资源
fileout.flush();
fileout.close();
stmt.close();
rs.close();
}
}