我已经编写了一个Java程序来从SQL中获取数据并将其写入Excel表,以下程序只有在我取消注释行时才有效。我不知道为什么会发生这种情况,请帮助我更正程序。
package com.selenium.examples;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SQLtoExcelSheet {
public static void main(String[] args) throws SQLException, IOException {
Workbook excel=new XSSFWorkbook();
Sheet sheet= excel.createSheet("SQLResults");
int excelRowNo=0;
int excelCellNo=0;
String connectionUrl = "jdbc:sqlserver://localhost;user=sa;password=abc@123";
Connection con=DriverManager.getConnection(connectionUrl);
Statement stmt=con.createStatement();
String SQL="use BonusingCore;"+"select * from MachineMaster;";
ResultSet result=stmt.executeQuery(SQL);
ResultSetMetaData mataData=result.getMetaData();
int columnCount=mataData.getColumnCount();
result.next();
//first row should be Column label
for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
Row excelRow=sheet.createRow(excelRowNo);
Cell excelCell=excelRow.createCell(excelCellNo);
excelCell.setCellValue(mataData.getColumnName(sqlColumnNumber));
excelCellNo++;
excelRowNo++;
sheet.autoSizeColumn(excelCellNo);
}
FileOutputStream flout=new FileOutputStream("C:\Users\Illusion\workspace\SeleniumExamples\testData\output.xlsx");
excel.write(flout);
flout.close();
}
}
如果要在任何特定行的某些列中写入一些数据,则需要一次性使用createRow()
,如果没有初始化,则需要始终使用createCell()
。
在代码中
for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
//creating a new row by createRow()... this will remove previous instance
Row excelRow=sheet.createRow(excelRowNo);
// creating a new column by createCell()
Cell excelCell=excelRow.createCell(excelCellNo);
......
似乎对于每个单元格,您都在一次又一次地创建同一行,这样以前的数据就会丢失。相反,您只需要创建一行,并使用同一行创建所有单元格。
例如
//creating a new row by createRow()
Row excelRow=sheet.createRow(excelRowNo);
for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
// creating a new column by createCell()
Cell excelCell=excelRow.createCell(excelCellNo);
希望你已经明白我想说的。。。祝你好运