📄 excel.java
字号:
dbConnect dbconn = new dbConnect(Code);
DBResultset rs = null;
int charge=0;
disctVerifyQuery queryinfo = new disctVerifyQuery(Code);
String Sql1="select TYPE_NAME,SERV_TYPE_ID from SERV_TYPE";
String[] f_serv_type = queryinfo.query_check(Sql1, Code);
String Sql2="select NAME,ACCT_ITEM_TYPE_ID from ACCT_ITEM_TYPE";
String[] f_acct_item_type = queryinfo.query_check(Sql2, Code);
// HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
// HSSFSheet sheet = wb.createSheet("sheet0");//建立新的sheet对象
String readfile = tmppath+"\\balance_verify_tmp.xls";
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(readfile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(1);
System.out.println(" EXCEL SQL： "+sql);
try{
rs = dbconn.runQuery(sql,1000);
count = rs.getRowSize();
System.out.println("COUNT IS: "+count);
short irow=0;
String str="";
HSSFRow row0 = sheet.createRow(irow);
str = "帐期";
HSSFCell csCell00 =row0.createCell((short)0);
csCell00.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell00.setCellValue(str);
str = "帐目类型";
HSSFCell acsCell10 =row0.createCell((short)1);
acsCell10.setEncoding(HSSFCell.ENCODING_UTF_16);
acsCell10.setCellValue(str);
str = "设备账类型";
HSSFCell csCell20 =row0.createCell((short)2);
csCell20.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell20.setCellValue(str);
str = "用户标识";
HSSFCell csCell30 =row0.createCell((short)3);
csCell30.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell30.setCellValue(str);
str = "接入号码";
HSSFCell csCell40 =row0.createCell((short)4);
csCell40.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell40.setCellValue(str);
str = "详单费用";
HSSFCell csCell50 =row0.createCell((short)5);
csCell50.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell50.setCellValue(str);
str = "账目费用";
HSSFCell csCell60 =row0.createCell((short)6);
csCell60.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell60.setCellValue(str);
str = "差额";
HSSFCell csCell70 =row0.createCell((short)7);
csCell70.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell70.setCellValue(str);
str = "是否确认";
HSSFCell csCell80 =row0.createCell((short)8);
csCell80.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell80.setCellValue(str);
irow ++;
while (rs.moveNext()) {
HSSFRow row = sheet.createRow(irow);
str = rs.fieldByName("BILLING_CYCLE_ID");
HSSFCell csCell0 =row.createCell((short)0);
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(str);
str = rs.fieldByName("ACCT_ITEM_TYPE_ID");
str = ComFuns.TypeIDtoName(str,f_acct_item_type);
HSSFCell csCell1 =row.createCell((short)1);
csCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell1.setCellValue(str);
str = rs.fieldByName("BELONG").trim();
if(str.equalsIgnoreCase("20"))
{
str = "设备账";
}
else if(str.equalsIgnoreCase("40"))
{
str = "代收费";
}
else if(str.equalsIgnoreCase("68"))
{
str = "信息费";
}
else
{
str = "其他";
}
HSSFCell csCell2 =row.createCell((short)2);
csCell2.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell2.setCellValue(str);
str = rs.fieldByName("SERV_TYPE_ID");
str = ComFuns.TypeIDtoName(str,f_serv_type);
HSSFCell csCell3 =row.createCell((short)3);
csCell3.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell3.setCellValue(str);
str = rs.fieldByName("ACC_NBR");
HSSFCell csCell4 =row.createCell((short)4);
csCell4.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell4.setCellValue(str);
str = rs.fieldByName("USAGE_CHARGE").trim();
HSSFCell csCell5 =row.createCell((short)5);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell5.setCellValue(charge);
str = rs.fieldByName("ACCT_ITEM_CHARGE").trim();
HSSFCell csCell6 =row.createCell((short)6);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell6.setCellValue(charge);
str = rs.fieldByName("BALANCE").trim();
HSSFCell csCell7 =row.createCell((short)7);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell7.setCellValue(charge);
str = rs.fieldByName("IS_VALIDATE");
if(str.equalsIgnoreCase("0"))
str="未确认";
else if(str.equalsIgnoreCase("1"))
str="确认";
else
str="未知状态";
HSSFCell csCell8 =row.createCell((short)8);
csCell8.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell8.setCellValue(str);
irow ++;
}
}
catch(Exception e)
{
System.out.println("--loginAction.checkUser系统错误!");
//e.printStackTrace();
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
wb.write(fileOut);
fileOut.close();
}
/**
*
* modify by songjx
* @param fileName String
* @param titleName String[]
* @param fieldName String[]
* @param sql String
* @param code String
* @throws IOException
*/
static public void excelWriteCJJF(
String fileName,
String[] titleName,
String[] fieldName,
String sql,
String code,String tmppath) throws IOException
{
int count=0;
int charge=0;
dbConnect dbconn = new dbConnect(code);
DBResultset rs = null;
String readfile = tmppath+"\\tp.xls";
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(readfile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(1);
try{
System.out.println("sql="+sql+" code="+code);
rs = dbconn.runQuery(sql,1000);
count = rs.getRowSize();
System.out.println("COUNT IS: "+count);
int irow =0;
int titlelen= titleName.length;
String str="";
HSSFRow row0 = sheet.createRow(irow);
for(int i=0; i<titlelen; i++)
{
HSSFCell csCell0 = row0.createCell( (short) i);
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(titleName[i]);
}
int len = fieldName.length;
irow++;
while (rs.moveNext()) {
HSSFRow row = sheet.createRow(irow);
for (int i = 0; i < len; i++)
{
str = rs.fieldByName(fieldName[i]);
HSSFCell csCell0 = row.createCell( (short) i);
if(rs.getColumnType(i)==0)
{
charge=Integer.parseInt(str);
csCell0.setCellValue(charge);
//System.out.println("整型列数为：："+(i+1));
}else if(rs.getColumnType(i)==1){
double columnValue=Double.parseDouble(str);
csCell0.setCellValue(columnValue);
//System.out.println("double列数为：："+(i+1));
}else{
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(str);
//System.out.println("字符串型列数为：："+(i+1));
}
}
irow++;
}
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
}catch(Exception e){
System.out.println("--excel错误!");
e.printStackTrace();
}
}
/**
*
* 通过查询数据库，把查询到的值保存到EXCEL表中
* @param excelFileName String EXCEL文件名
* @param sql String 执行的SQL语句
* @throws IOException
*/
static public void excelWriteHD(String excelFileName, String StrQ,String Code, String tmppath) throws IOException
{
int count=0;
dbConnect dbconn = new dbConnect(Code);
DBResultset rs = null;
String sql="";
int charge=0;
float fei=0;
// HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
// HSSFSheet sheet = wb.createSheet("sheet0");//建立新的sheet对象
String readfile = tmppath+"\\zwYH_tmp.xls";
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(readfile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(1);
System.out.println("SQL语句测试： "+sql);
try{
rs = dbconn.runQuery(StrQ,1000);
count = rs.getRowSize();
System.out.println("COUNT IS: "+count);
short irow=0;
String str="";
HSSFRow row0 = sheet.createRow(irow);
str = "帐期类型";
HSSFCell csCell00 =row0.createCell((short)0);
csCell00.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell00.setCellValue(str);
str = "稽核类型";
HSSFCell acsCell10 =row0.createCell((short)1);
acsCell10.setEncoding(HSSFCell.ENCODING_UTF_16);
acsCell10.setCellValue(str);
str = "上月总额";
HSSFCell csCell20 =row0.createCell((short)2);
csCell20.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell20.setCellValue(str);
str = "总额";
HSSFCell csCell30 =row0.createCell((short)3);
csCell30.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell30.setCellValue(str);
str = "差额";
HSSFCell csCell40 =row0.createCell((short)4);
csCell40.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell40.setCellValue(str);
str = "差额百分比";
HSSFCell csCell50 =row0.createCell((short)5);
csCell50.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell50.setCellValue(str);
str = "是否检验";
HSSFCell csCell60 =row0.createCell((short)6);
csCell60.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell60.setCellValue(str);
irow ++;
while (rs.moveNext()) {
HSSFRow row = sheet.createRow(irow);
str = rs.fieldByName("BILLING_CYCLE_ID");
HSSFCell csCell0 =row.createCell((short)0);
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(str);
str = rs.fieldByName("CHECK_TYPE");
HSSFCell csCell1 =row.createCell((short)1);
csCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell1.setCellValue(str);
str = rs.fieldByName("LAST_TOTAL");
HSSFCell csCell2 =row.createCell((short)2);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell2.setCellValue(charge);
str = rs.fieldByName("TOTAL");
HSSFCell csCell3 =row.createCell((short)3);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell3.setCellValue(charge);
str = rs.fieldByName("BALANCE");
HSSFCell csCell4 =row.createCell((short)4);
if(str.equalsIgnoreCase(""))
{
charge=0;
}
else
charge=Integer.parseInt(str);
csCell4.setCellValue(charge);
str = rs.fieldByName("PERCENT");
HSSFCell csCell5 =row.createCell((short)5);
if(str.equalsIgnoreCase(""))
{
fei=0;
}
else
fei=Float.parseFloat(str);
csCell5.setCellValue(fei);
str = rs.fieldByName("IS_VALIDATE");
if(str.equalsIgnoreCase("2"))
str="未检验";
else
str="检验";
HSSFCell csCell6 =row.createCell((short)6);
csCell6.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell6.setCellValue(str);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -