Excel导入导出数据到SQL数据库的实例。

[ITzhangwei]

大侠们，我这两天在学Excel的导入导出，知道了有一些SQL语句可以实现导入导出，但是让我写个程序，我不知道JSP页面或者说是整个工程不知道该怎么写，希望谁能给我说个思路，或者写个例子，帮帮忙，谢谢！！

[tdy1234]

可以的话就结贴吧 呵呵

[liujun999999]

用sqlserver的DTS比上面的好像都要方便吧

[tdy1234]

不过可能是说用命令行方式把一个数据库表转换为excel表。

楼主想要查询结果集转换为excel表，我知道有两种方式：一种是转换为csv格式（这是Excel的一种格式）。这种格式是纯文本，文本的一行对应于Excel表的一行，不同的列之间用“,”分隔。
另外一种方式就是利用java操作excel的API，比如Jakarta POI，从而可以对excel文件进行读写。可参见：http://jakarta.apache.org/poi/

[ITzhangwei]

另外一种方式就是利用java操作excel的API，比如Jakarta POI，从而可以对excel文件进行读写。可参见：http://jakarta.apache.org/poi/

我说的就是这个方法，但是我刚刚接触，都不知道从何入手，请详细指点一下吧！
拜托~

[Terrylx]

我用的是jxl，感觉不错，你可以去看看实例
http://www.1to2.us/Java-Excel-a159665.htm

[tdy1234]

//建立输出表实例
_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();

你看看这段代码吧 注释的很详细了

[ITzhangwei]

有excel和sql的导入导出的例子吗？

[tdy1234]

readChannel(String inputDate, String branchCode,
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' ";

[tdy1234]

pstmt1 = con.prepareStatement(sql2);
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作的

[tdy1234]

LZ自己看看吧最好自己研究透彻 倒入的话建议把excel 转换为cvs进行倒入

[ITzhangwei]

谢谢， 太谢谢了！

[ITzhangwei]

不是我不想结贴，是我在管理那结贴，他说我权限不足，不知道我是不是太笨了。

[tdy1234]

结贴要输入密码的。。。。

[tdy1234]

LZ麻烦结贴 谢谢

[Ji秋风]

建议楼主用DTS，在程序用dtsrunui调用DTS包。