📄 excel.java
字号:
package com.dreamtel.global;
import org.apache.poi.hssf.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import com.dreamtel.db.conn.*;
import com.dreamtel.db.dbresult.DBResultset;
import com.dreamtel.jhAction.zhangwu.verify.*;
import javax.servlet.http.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.dreamtel.global.ComFuns;
/**
*
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2004</p>
* <p>Company: dreamtel</p>
* @author xiexp
* @version 1.0
* modify by songjx 20041028 实现数值型转换，方法是excelWriteCJJF（）
*/
public class excel {
public excel() {
}
/**
*
* 建立EXECL文件，并把数据保存到文件中
* @param excelFileName String EXCEL文件名
* @throws IOException
*/
static public void excelWrite(String excelFileName) throws IOException
{
HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);//建立新行
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short)0);//建立新cell
cell.setCellValue(1);//设置cell的整数类型的值
// Or do it on one line.
row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值
row.createCell((short)2).setCellValue("test");//设置cell字符类型的值
row.createCell((short)3).setCellValue(true);//设置cell布尔类型的值
HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//设置cell样式为定制的日期格式
HSSFCell dCell =row.createCell((short)4);
dCell.setCellValue(new Date());//设置cell为日期类型的值
dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式
HSSFCell csCell =row.createCell((short)5);
csCell.setEncoding(HSSFCell.ENCODING_UTF_16);//设置cell编码解决中文高位字节截断
csCell.setCellValue("中文测试_Chinese Words Test");//设置中西文结合字符串
row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);//建立错误cell
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(excelFileName);
wb.write(fileOut);
fileOut.close();
}
/**
*
* 通过查询数据库，把查询到的值保存到EXCEL表中
* @param excelFileName String EXCEL文件名
* @param sql String 执行的SQL语句
* @throws IOException
*/
static public void excelWriteZwYh(String excelFileName, String StrQ,String code, String tmppath) throws IOException
{
int count=0;
dbConnect dbconn = new dbConnect(code);
DBResultset rs = null;
DBResultset rs1 = null;
String strQ="";
String sql="";
// 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);
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);
str = "所属客户";
HSSFCell csCell90 =row0.createCell((short)9);
csCell90.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell90.setCellValue(str);
str = "标识";
HSSFCell csCell100 =row0.createCell((short)10);
csCell100.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell100.setCellValue(str);
str = "电话号码";
HSSFCell csCell110 =row0.createCell((short)11);
csCell110.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell110.setCellValue(str);
str = "营业区标识";
HSSFCell csCell120 =row0.createCell((short)12);
csCell120.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell120.setCellValue(str);
str = "检验";
HSSFCell csCell130 =row0.createCell((short)13);
csCell130.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell130.setCellValue(str);
irow ++;
while (rs.moveNext()) {
HSSFRow row = sheet.createRow(irow);
str = rs.fieldByName("SERV_ID");
HSSFCell csCell0 =row.createCell((short)0);
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(str);
str = rs.fieldByName("DISCT_RULE_ID");
HSSFCell csCell1 =row.createCell((short)1);
csCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell1.setCellValue(str);
str = rs.fieldByName("SOURCE");
HSSFCell csCell2 =row.createCell((short)2);
csCell2.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell2.setCellValue(str);
// str = rs.fieldByName("ACCT_ITEM_TYPE_ID");
strQ="select NAME from ACCT_ITEM_TYPE WHERE ACCT_ITEM_TYPE_ID="+rs.fieldByName("ACCT_ITEM_TYPE_ID");
rs1=dbconn.runQuery(strQ,1000);
// rs1.moveFirst();
HSSFCell csCell3 =row.createCell((short)3);
csCell3.setEncoding(HSSFCell.ENCODING_UTF_16);
while(rs1.moveNext())
{
// System.out.println(rs1.fieldByName("NAME"));
csCell3.setCellValue(rs1.fieldByName("NAME"));
}
str = rs.fieldByName("INTERNAL_CODE");
HSSFCell csCell4 =row.createCell((short)4);
csCell4.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell4.setCellValue(str);
str = rs.fieldByName("ACCT_ITEM_TYPE_GROUP_ID");
HSSFCell csCell5 =row.createCell((short)5);
csCell5.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell5.setCellValue(str);
str = rs.fieldByName("REP_LEVEL");
if(str.equalsIgnoreCase("1"))
str="规则级重复";
else
str="优惠级重复";
HSSFCell csCell6 =row.createCell((short)6);
csCell6.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell6.setCellValue(str);
str = rs.fieldByName("ACCT_ID");
HSSFCell csCell7 =row.createCell((short)7);
csCell7.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell7.setCellValue(str);
str = rs.fieldByName("NBR_GROUP_ID");
HSSFCell csCell8 =row.createCell((short)8);
csCell8.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell8.setCellValue(str);
str = rs.fieldByName("CUST_ID");
HSSFCell csCell9 =row.createCell((short)9);
csCell9.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell9.setCellValue(str);
str = rs.fieldByName("FLAG");
HSSFCell csCell10 =row.createCell((short)10);
csCell10.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell10.setCellValue(str);
str = rs.fieldByName("ACC_NBR");
HSSFCell csCell11 =row.createCell((short)11);
csCell11.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell11.setCellValue(str);
// str = rs.fieldByName("AREA_ID");
strQ="select NAME from AREA where AREA_ID_CODE ='"+rs.fieldByName("AREA_ID")+"'";
rs1=dbconn.runQuery(strQ,1000);
// rs1.moveFirst();
HSSFCell csCell12 =row.createCell((short)12);
csCell12.setEncoding(HSSFCell.ENCODING_UTF_16);
while(rs1.moveNext())
{
// System.out.println(rs1.fieldByName("NAME"));
csCell12.setCellValue(rs1.fieldByName("NAME"));
}
str =rs.fieldByName("IS_VALIDATE");
if(str.equalsIgnoreCase("2"))
str ="未确认";
else
str ="确认";
HSSFCell csCell13 =row.createCell((short)13);
csCell13.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell13.setCellValue(str);
irow ++;
}
}
catch(Exception e)
{
System.out.println("--loginAction.checkUser系统错误!");
//e.printStackTrace();
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
wb.write(fileOut);
fileOut.close();
}
static public void excelWrite(
String fileName,
String[] titleName,
String[] fieldName,
String sql,
String code) throws IOException
{
int count=0;
dbConnect dbconn = new dbConnect(code);
DBResultset rs = null;
HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet("sheet0");//建立新的sheet对象
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++) {
// System.out.println("rowname["+i+"]=="+colName[i+1]);
str = rs.fieldByName(fieldName[i]);
HSSFCell csCell0 = row.createCell( (short) i);
csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell0.setCellValue(str);
}
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 excelWriteBalanceVerify(String excelFileName, String sql,String Code, String tmppath) throws IOException
{
int count=0;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -