Java对cxcel表的读些主要是通过Apache POI的支持。
- Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache
POI提供API给Java程式对Microsoft Office格式档案读和写的功能。 -
Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE
2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS
Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案。
包链接http://poi.apache.org/index.html
需要导入的jar包
poi-3.15-beta2.jar
poi-ooxml-3.15-beta2.jar(07版以后需要的包)
poi-ooxml-schemas-3.15-beta2.jar(07版以后需要的包)
ooxml-lib目录下的 xmlbeans-2.6.0.jar (07版以后需要的包)
1、在本地创建一个表格
@Test
public void createXlsx() throws FileNotFoundException, IOException{
Workbook book=new XSSFWorkbook();
Sheet sheet =book.createSheet();
Row row3=sheet.createRow(3);
Cell cell4=row3.createCell(4);
cell4.setCellValue("湖南城市学院");
book.write(new FileOutputStream("a.xlsx"));
}
2、从一个数据库导入另一个一个数据库的所有表格信息(需要用到元数据的操作),关于元数据请参考http://blog.csdn.net/wangjian_an/article/details/52171466
package cn.hncu.meta
import java.io.FileNotFoundException
import java.io.FileOutputStream
import java.io.IOException
import java.sql.Connection
import java.sql.DatabaseMetaData
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.ResultSetMetaData
import java.sql.SQLException
import java.sql.Statement
import java.util.ArrayList
import java.util.List
import javax.xml.crypto.dsig.XMLObject
import org.apache.poi.hssf.usermodel.HSSFWorkbook
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
import org.junit.Test
public class ExportXlsxDemo {
@Test
public void ExportXlsx() {
String databaseName="sstud"
String filename="sstud.xlsx"
try {
export(databaseName, filename)
} catch (SQLException e) {
e.printStackTrace()
}
}
private void export(String databaseName,String filename) throws SQLException {
String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8"
String user="root"
String password="1234"
Connection con=DriverManager.getConnection(url, user, password)
DatabaseMetaData dmd=con.getMetaData()
ResultSet rs=dmd.getTables("sstud", "sstud", "", new String[]{"TABLE"})
List<String> list=new ArrayList<String>()
while(rs.next()){
list.add(rs.getString("TABLE_NAME"))
}
Statement st=con.createStatement()
Workbook book=new XSSFWorkbook()
for(String tbName:list){
String sql="select * from "+databaseName+"."+tbName
ResultSet rs2=st.executeQuery(sql)
ResultSetMetaData rsmd=rs2.getMetaData()
int coloums=rsmd.getColumnCount()
Sheet sheet =book.createSheet(tbName)
int rowNum=0
Row row=sheet.createRow(rowNum++)
for(int i=0
Cell cell=row.createCell(i)
cell.setCellValue(rsmd.getColumnName(i+1))
}
while(rs2.next()){
Row row2=sheet.createRow(rowNum++)
for(int i=0
Cell cell2=row2.createCell(i)
cell2.setCellValue(rs2.getString(i+1))
}
}
}
try {
book.write(new FileOutputStream(filename))
} catch (FileNotFoundException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83