jdbc mysql excel_读EXCEL、数据库数据写入EXCEL(POI,输入输出流，JDBC)

导读：通过POI，可以方便的读写EXCEL,WORD等文本文件。本文演示了如何进行EXCEL的读写。

1.将数据库的数据写入EXCEL

(带有中表头；自动分为多个sheet，如果数据较大)

import

java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import

org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JDBCWriteMyExcel {

private static Connection conn;

private static ResultSet rs;

private int sheetNumber = 11;

private HSSFSheet[] hSSFSheets = new

HSSFSheet[sheetNumber];

private HSSFRow hSSFRow;

private HSSFCell hSSFCell;

private static int startNumber;

private static int endNumber;

String sql = "";

// 当前sheet的下标，第一个sheet i为0.

private static int i = 0;

private static final int SHEET_ROW_NUMBER

= 3000;

private static final int

SELECT_EACH_NUMBER = 10 * 1000;

public

static void main(String[] args) throws Exception {

JDBCWriteMyExcel

jDBCWriteMyExcel = new JDBCWriteMyExcel();

endNumber =

startNumber + SELECT_EACH_NUMBER;

for (int l = 0; l

< 1; l++) {

rs

= jDBCWriteMyExcel.getResultSetFromDB(l);

jDBCWriteMyExcel.writeToExcel(rs,

"myExcell2011.xls");

rs

= null;

}

}

public static Connection getConn() throws

Exception {

//

driver,url,username, password

String driver =

"com.microsoft.sqlserver.jdbc.SQLServerDriver";

String url =

"jdbc:sqlserver://localhost:1433;DatabaseName=UDM";

String user =

"sa";

String password =

"sd100301";

Class.forName(driver);

return

DriverManager.getConnection(url, user, password);

}

public ResultSet getResultSetFromDB(int l)

throws Exception {

if (conn == null)

{

conn

= getConn();

}

// select

row_number() over(order by field1) as row_number,* from

// t_table

startNumber =

SELECT_EACH_NUMBER * l;

sql = " select *

from("

+

" select row_number() over(order by stkunicode) as row_number, *

from HK_STKMMK"

+

") as hmmk " + "where row_number between " + startNumber

+

" and " + endNumber;

Statement stmt =

conn.createStatement();

rs =

stmt.executeQuery(sql);

System.out.println("rs.getFetchSize():"

+ rs.getFetchSize());

return rs;

}

public void writeToExcel(ResultSet rs,

String fileName) throws Exception {

File

dir = new File("f:\myExcel\myExcel");

dir.mkdirs();

File file = new

File(dir.getCanonicalFile() + "\" + fileName);

// 得到流

FileOutputStream

fis = new FileOutputStream(file, true);

// 创建工作薄

HSSFWorkbook

hSSFWorkbook = new HSSFWorkbook();

// 创建sheet

hSSFSheets[0] =

hSSFWorkbook.createSheet();

while (rs.next())

{

System.out.println("rs.getRow():"

+ rs.getRow());

if

(rs.getRow() >= SHEET_ROW_NUMBER

&&

(rs.getRow() - SHEET_ROW_NUMBER * i) % SHEET_ROW_NUMBER == 0)

{

i++;

hSSFSheets[i]

= hSSFWorkbook.createSheet();

System.out.println("

hSSFSheets[]" + i + ":" + hSSFSheets[i]);

}

//

创建sheet的特定一行

//

如果是第一页

if

(i == 0) {

hSSFRow

= hSSFSheets[i].createRow((rs.getRow()) - 1 + 1);

//

hSSFRow = hSSFSheets[i].createRow((rs.getRow()) - 1); OK

//

如果是其他页

}

else if ((rs.getRow() - SHEET_ROW_NUMBER * i + 1) - 1

> 0) {

hSSFRow

= hSSFSheets[i].createRow((rs.getRow()

-

SHEET_ROW_NUMBER * i + 1) - 1);

}

//

else{

//

hSSFRow = hSSFSheets[i].createRow(rs.getRow() - 1);

//

}

//

遍历特定行的列

System.out.println("rs.getMetaData().getColumnCount():"

+

rs.getMetaData().getColumnCount());

for

(int k = 0; k <= rs.getMetaData().getColumnCount();

k++) {

//

创建一个单元格

hSSFCell

= hSSFRow.createCell(k);

//

取得结果集的具体记录，赋值给单元格

if

(k < rs.getMetaData().getColumnCount()) {

hSSFCell.setCellValue(rs.getString(k

+ 1));

}

}

}

for (int h = 0; h

< sheetNumber && h

<= i; h++) {

if

(rs.getRow() == 1 || rs.getRow() % SHEET_ROW_NUMBER == 0) {

hSSFRow

= hSSFSheets[h].createRow(0);

System.out.println("hSSFRow:

" + hSSFRow);

for

(int k = 0; k <= rs.getMetaData().getColumnCount();

k++) {

//

创建一个单元格

hSSFCell

= hSSFRow.createCell(k);

//

取得结果集的具体记录，赋值给单元格

if

(k < rs.getMetaData().getColumnCount()) {

hSSFCell.setCellValue(rs.getMetaData().getColumnName(

k

+ 1));

}

}

}

}

hSSFWorkbook.write(fis);

fis.close();

//

conn.close();

}

public

int getStartNumber() {

return

startNumber;

}

public

int getEndNumber() {

return

endNumber;

}

public

void setStartNumber(int startNumber) {

this.startNumber

= startNumber;

}

public

void setEndNumber(int endNumber) {

this.endNumber =

endNumber;

}

}

//

2.读EXCEL

import java.io.BufferedInputStream;

import java.io.BufferedReader;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.text.SimpleDateFormat;

import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Excel {

public static void main(String[] args)

{

System.out.println(getContent("F:\Lucene\udmcvs\udm.xls"));

//System.out.println(getContent("F:\Lucene\udm.xls"));

}

public static String getContent(String

s) {

try {

return

getContent(new java.io.FileInputStream(s));

} catch

(FileNotFoundException e) {

e.printStackTrace();

return

null;

}

}

public static String getContent(File f)

{

try {

return

getContent(new java.io.FileInputStream(f));

} catch

(FileNotFoundException e) {

e.printStackTrace();

return

null;

}

}

public static String

getContent(InputStream is) {

StringBuffer

content = new StringBuffer();

//

获得一个特定的工作薄

try {

BufferedInputStream

bis=new BufferedInputStream(is);//实例化一个建立在节点流基础上的bis对象，

InputStreamReader

isr=new InputStreamReader(bis);

BufferedReader

br=new BufferedReader(isr);

//br.readLine();

byte

BufferedInput_byte[]=new byte[1000*1024];

int

i=0;

// while(i!=-1){

//  i=bis.read(BufferedInput_byte);

//}

HSSFWorkbook

workbook=null;

try{

workbook = new HSSFWorkbook(bis);

}catch(Exception

ee){

ee.printStackTrace();

}

//

循环获得每一个sheet

for

(int numSheets = 0;

workbook!=null&&numSheets

< workbook.getNumberOfSheets(); numSheets++) {

//

获得一个sheet

HSSFSheet

aSheet = workbook.getSheetAt(numSheets);

bis.close();

content.append("n");

if

(null == aSheet) {

continue;

}

//

循环每一行

for

(int rowNum = 0; rowNum <= aSheet.getLastRowNum();

rowNum++) {

//

得到特定的一行

content.append("n");

HSSFRow

aRow = aSheet.getRow(rowNum);

if(null==aRow){

continue;

}

//

循环特定行的每一列

for

(int cellNum = 0; cellNum <= aRow.getLastCellNum();

cellNum++) {

//

得到特定单元格

content.append("

");

HSSFCell aCell=aRow.getCell(cellNum);

// 根据不同的单元格内容，进行判断，并取得其内容