大侠们,我这两天在学Excel的导入导出,知道了有一些SQL语句可以实现导入导出,但是让我写个程序,我不知道JSP页面或者说是整个工程不知道该怎么写,希望谁能给我说个思路,或者写个例子,帮帮忙,谢谢!!
解决方案 »
- 请问该怎样在方法run()中调用方法void outstock(Graphics g)????
- 急:范型中如何创建对象?
- 在windows下设的那个环境变量和在Dos窗口里用-classpath以及set classpath这三个环境变量会相互替换么?
- CardLayout布局??
- 如何编写3D图形程序
- 请问在哪里下载eclipse啊??
- 我是一个java初学者,请问JCreator有中文版吗?在哪里可以下载?
- java中可以这样初始化一个数组String[][] s = new String[3][];这样的东西在实际应用中有什么用呀!是不是为了考试,请高手指教。
- 新上任,散分给大家~~~
- 关于流的问题
- Applet问题:在applet中弹出一个新的ie! 谢谢 !请不吝赐教!
- 如何判断一个String是否包含在另一个String中
另外一种方式就是利用java操作excel的API,比如Jakarta POI,从而可以对excel文件进行读写。可参见:http://jakarta.apache.org/poi/
拜托~
http://www.1to2.us/Java-Excel-a159665.htm
_workbook_out = new HSSFWorkbook();
//以后关闭要用实例fileOut
fileOut = new FileOutputStream(outfilename);
//建立表格实例
HSSFSheet sheet_out = _workbook_out.createSheet();
rows 结果集中的记录数
for (int r = 0; r < rows; r++) //row numbers
{//建立行
HSSFRow row_out = sheet_out.createRow(r);
//建立该行列循环
for (short c = 0; c < cells; c++) {
//建立输出单元格对象实例
HSSFCell cell_out = row_out.createCell(c);
//单元格汉字编码转换
cell_out.setEncoding(HSSFCell.ENCODING_UTF_16);
//设置单元格属性
cell_out.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//添值(这个值就是你准备要取的结果集中的某个你要放置的值)
cell_out.setCellValue(numvalue);
}//列循环结束} //行循环结束//最后处理输出工作表关闭写入数据
_workbook_out.write(fileOut);
fileOut.close();你看看这段代码吧 注释的很详细了
Connection con,
String toFileName, String templateName,
String reportName, String errorMessage) {
System.out.println("==========" + toFileName);
if (sys_logger.isDebugEnabled()){
sys_logger.debug(toFileName + " begin...");
sys_logger.debug("---from " + templateName);
}
Object synObj;
if (synFile.get(templateName) == null) {
synObj = new Object();
synFile.put(templateName, synObj);
} synObj = synFile.get(templateName);
synchronized (synObj) { POIFSFileSystem pfs = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow hr = null;
HSSFCell cell = null; FileOutputStream fos = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt1 = null;
ResultSet rs = null; String sheetname = null;
String col = null;
String row = null;
String num = null;
String celltype = null;
int i = 0;
try { File filedelete = new File(toFileName);
if (filedelete.exists()) {
filedelete.delete();
} //xls定义
pfs = new POIFSFileSystem(new FileInputStream(templateName));
workbook = new HSSFWorkbook(pfs); String sql =
"select sheetname,colpos,rowpos,num,celltype from tbrptresult "
+ "where branch_code=? and reportname=? "
+ "order by sheetname,to_number(colpos),to_number(rowpos)"; pstmt = con.prepareStatement(sql);
pstmt.setString(1, branchCode);
pstmt.setString(2, reportName);
rs = pstmt.executeQuery(); //sheet = copy.getSheet(Integer.parseInt("1")); while (rs.next()) { sheetname = rs.getString(1); //in fact sheetname is sheetid
col = rs.getString(2);
row = rs.getString(3);
num = StringUtils.encode(rs.getString(4));
celltype = rs.getString(5); //不带格式,只修改数值
sheet = workbook.getSheetAt(Integer.parseInt(sheetname));
hr = sheet.getRow(Integer.parseInt(row));
cell = hr.getCell( (short) (Integer.parseInt(col)));
if (num != null) {
i++;
//logger.debug(i + ":(" + row + ":" + col + ":" + num +":" + cell.getCellType() + ")"); if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
cell.setCellValue( (new Double(num)).doubleValue());
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK ||
cell.getCellType() ==
HSSFCell.CELL_TYPE_STRING) {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding( (short) 1);
cell.setCellValue(num);
}
} cell = null;
hr = null;
sheet = null; //LoggerUtils.recordError(ReportConstants.REPORT_SYS_LOG, "6-991" + copy.toString() );
} //去掉多余行 begin
if (!PreLoader.HQ_BRANCH_CODE.equals(branchCode)){
String hassub = "";
String day = inputDate.substring(8, 10);
String sql2 =
"select hassubbranch from t_rpt_branch where branch_code=? " +
" and status='1' ";
pstmt1.setString(1, branchCode);
//pstmt1.setString(2, reportName);
rs = pstmt1.executeQuery();
while (rs.next()) {
hassub = rs.getString(1);
}
if (hassub.equalsIgnoreCase("0")) {
if (reportName.equalsIgnoreCase("DAILYRPT3")) {
hiderow(2, 9, day, workbook);
hiderow(3, 11, day, workbook);
hiderow(4, 10, day, workbook);
hiderow(5, 11, day, workbook);
hiderow(6, 10, day, workbook);
hiderow(7, 10, day, workbook);
hiderow(8, 6, day, workbook);
hiderow(9, 6, day, workbook);
hiderow(10, 6, day, workbook);
hiderow(11, 6, day, workbook);
hiderow(12, 6, day, workbook);
}
if (reportName.equalsIgnoreCase("DAILYRPT3AG") ||
reportName.equalsIgnoreCase("DAILYRPT3BANC")) {
hiderow(2, 11, day, workbook);
hiderow(3, 6, day, workbook);
}
if (reportName.equalsIgnoreCase("DAILYRPT3BRK") ||
reportName.equalsIgnoreCase("DAILYRPT3DM") ||
reportName.equalsIgnoreCase("DAILYRPT3GODT")) {
hiderow(2, 10, day, workbook);
hiderow(3, 6, day, workbook);
}
}
if (hassub.equalsIgnoreCase("1")) {
if (reportName.equalsIgnoreCase("DAILYRPT3")) {
hiderow(2, 9, day, workbook);
hiderow(3, 11, day, workbook);
hiderow(4, 11, day, workbook);
hiderow(5, 11, day, workbook);
hiderow(6, 11, day, workbook);
hiderow(7, 11, day, workbook);
hiderow(8, 6, day, workbook);
hiderow(9, 6, day, workbook);
hiderow(10, 6, day, workbook);
hiderow(11, 6, day, workbook);
hiderow(12, 6, day, workbook);
}
if (reportName.equalsIgnoreCase("DAILYRPT3AG") ||
reportName.equalsIgnoreCase("DAILYRPT3BANC")) {
int ns = workbook.getNumberOfSheets();
for (int k = 2; k < ns; k++) {
hiderow(k, 11, day, workbook);
}
//hiderow(2, 11, day, workbook);
//hiderow(3, 6, day, workbook);
}
if (reportName.equalsIgnoreCase("DAILYRPT3BRK") ||
reportName.equalsIgnoreCase("DAILYRPT3DM") ||
reportName.equalsIgnoreCase("DAILYRPT3GODT")) {
int ns = workbook.getNumberOfSheets();
for (int k = 2; k < ns; k++) {
hiderow(k, 10, day, workbook);
}
//hiderow(2, 10, day, workbook);
//hiderow(3, 6, day, workbook);
} }
}
//end fos = new FileOutputStream(new File(toFileName));
workbook.write(fos);
fos.flush();
System.out.println("from " + templateName);
System.out.println(toFileName + " genarate over");
if (sys_logger.isDebugEnabled()){
sys_logger.debug(toFileName + " genarate over");
}
}
//catch (Throwable t) {
catch (Exception t) {
t.printStackTrace();
sys_logger.error(i + ":Exception(" + row + ":" + col + ":" + num + ":" +
cell.getCellType() + ")");
sys_logger.error(t);
}
finally { try {
if (fos != null) {
fos.close();
}
}
catch (Exception e) {
e.printStackTrace();
}
workbook = null;
pfs = null;
//LoggerUtils.recordError(ReportConstants.REPORT_WEB_LOG, "3" + reportName);
ConnectionPool.releaseResultSet(rs);
ConnectionPool.releasePreparedStatement(pstmt);
ConnectionPool.releasePreparedStatement(pstmt1);
}
}
}
这是我前面写的一个函数是从数据库中读记录写入EXCEL 你参考一下,用POI作的