用jxl将mysql的数据结构导入到excel中
来源:互联网 发布:口袋操作系统 linux 编辑:程序博客网 时间:2024/04/28 00:31
效果图如下:
package com.example.test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class HelperTest3 {
static WritableWorkbook wwb = null;
static OutputStream os = null;
private int row=0;
static{
String filePath = "D:\\excel\\Test.xls";
File file = new File(filePath);
try {
if(!file.isFile()){//如果指定文件不存在,则新建该文件
file.createNewFile();
}
os = new FileOutputStream(file);//创建一个输出流
wwb = Workbook.createWorkbook(os);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
static List<String> listName=new ArrayList<String>();
static List<String> listType=new ArrayList<String>();
static List<String> listLength=new ArrayList<String>();
static List<String> listNum=new ArrayList<String>();
static List list=new ArrayList();
public static void main(String[] args) {
//序号 列名 数据类型 长度 小数位 标识 主键 外键 允许空 默认值 说明
String user="root";
String password="";
String url="jdbc:mysql://localhost:3306/user";
String driver="com.mysql.jdbc.Driver";
String tableName="Book";
String sqlstr;
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
boolean isLast=false;
try {
Class.forName(driver);
con=DriverManager.getConnection(url,user,password);
stmt=con.createStatement();
list = new HelperTest3().getAllTableName(con);
listName.add("列名");
listType.add("字段类型");
listLength.add("长度");
listNum.add("序号");
for (int iTable = 0; iTable < list.size(); iTable++) {
tableName=(String) list.get(iTable);
sqlstr="select * from "+tableName;
rs=stmt.executeQuery(sqlstr);
ResultSetMetaData rsd=rs.getMetaData();
for(int i = 0; i < rsd.getColumnCount(); i++) {
listName.add(rsd.getColumnName(i + 1));
listType.add(rsd.getColumnTypeName(i + 1));
listLength.add (String.valueOf(rsd.getColumnDisplaySize(i + 1)));
listNum.add (String.valueOf(i+1));
System.out.print("列名:"+rsd.getColumnName(i + 1));
System.out.print("字段类型:"+rsd.getColumnTypeName(i + 1));
System.out.print("长度:"+rsd.getColumnDisplaySize(i + 1));
System.out.println();
}
System.out.println("tableName:"+tableName);
System.out.println(listName.toString());
System.out.println(listType.toString());
System.out.println(listLength.toString());
System.out.println(listNum.toString());
isLast=(iTable==list.size()-1?true:false);
new HelperTest3().addToExcel(listName, listType, listLength,listNum,tableName,iTable,isLast);
listName.clear();
listType.clear();
listLength.clear();
listNum.clear();
listName.add("列名");
listType.add("字段类型");
listLength.add("长度");
listNum.add("序号");
}
try {
System.out.println("成功");
// System.out.println("sheet.getRows()"+sheet.getRows());
wwb.write();//将内容写到excel文件中
os.flush();//清空输出流
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(wwb != null)
wwb.close();
if(os != null)
os.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
// TODO: handle exception
}
}
public void addToExcel(List<String> listName,List<String> listType,
List<String> listLength,List<String> listNum,String tableName,int k, boolean isLast){
WritableSheet sheet = null;
try {
// sheet = wwb.createSheet("sheet1", 0);//创建一个工作页,第一个参数的页名,第二个参数表示该工作页在excel中处于哪一页
//创建sheet之后就不能找到以前的sheet?
// sheet = wwb.createSheet("sheet1", 0);//创建一个工作页,第一个参数的页名,第二个参数表示该工作页在excel中处于哪一页
// sheet = wwb.createSheet("sheet2", 0);//创建一个工作页,第一个参数的页名,第二个参数表示该工作页在excel中处于哪一页
if (k==0) {
sheet = wwb.createSheet("sheet1", 0);
}else {
sheet=wwb.getSheet(0);
}
/*String[] sNames = wwb.getSheetNames();
for (int i = 0; i < sNames.length; i++) {
System.out.println("///////");
System.out.println(sNames[i]+"\n");
}*/
row=sheet.getRows();
creatBasicData(listName, listType, listLength, listNum, tableName,
sheet,row);
//生成第一栏
creatTop(sheet,row);
//正常style
creatNomorlStyle(listName, sheet,row);
System.out.println("sheet.rows"+sheet.getRows());
} catch (WriteException e) {
e.printStackTrace();
} finally {
}
}
private void creatBasicData(List<String> listName, List<String> listType,
List<String> listLength, List<String> listNum, String tableName,
WritableSheet sheet, int row) throws WriteException, RowsExceededException {
System.out.println("此时的row为"+row);
//第一个参数表示列,第二个参数表示行
Label label = new Label(0,0+row,tableName);//填充第一行第一个单元格的内容
sheet.addCell(label);
for (int i = 0; i < listName.size(); i++) {
//i+1行是为了让出第一行的表名
WritableCellFormat wcfFC = testStyle(i);
label = new Label(0,i+1+row,listNum.get(i),wcfFC);//定制单元格格式,//填充第二列,以此类推
sheet.addCell(label);
label = new Label(1,i+1+row,listName.get(i),wcfFC);
sheet.addCell(label);
label = new Label(2,i+1+row,listType.get(i),wcfFC);
sheet.addCell(label);
label = new Label(3,i+1+row,listLength.get(i),wcfFC);
sheet.addCell(label);
}
}
private void creatNomorlStyle(List<String> listName, WritableSheet sheet, int row)
throws WriteException, RowsExceededException {
Label label;
List<String>list=new ArrayList<String>();
for (int i = 0; i < listName.size()-1; i++) {
list.add(i==0?"否":"是");
}
WritableCellFormat wcfFC = testStyle(1);
for (int i = 4; i <= 10; i++) {
for (int j = 2; j <= listName.size(); j++) {
if (i==8) {
label = new Label(i,j+row,list.get(j-2),wcfFC);//填充第8列的元素i=8
}else {
if (i==6&&j==2) {
label = new Label(i,j+row,"是",wcfFC);//填充第一行第一个单元格的内容
}else {
label = new Label(i,j+row,"",wcfFC);//填充第一行第一个单元格的内容
}
}
sheet.addCell(label);
}
}
}
private void creatTop(WritableSheet sheet, int row) throws WriteException,
RowsExceededException {
Label label;
//小数位 标识 主键 外键 允许空 默认值 说明
//4 5 6 7 8 9 10
List<String>list=new ArrayList<String>();
list.add("小数位");
list.add("标识");
list.add("主键");
list.add("外键");
list.add("允许空");
list.add("默认值");
list.add("说明");
//粗体加背景
WritableCellFormat wcfFC = testStyle(0);
for (int i = 0; i < list.size(); i++) {
label = new Label(i+4,1+row,list.get(i),wcfFC);//填充第一行第一个单元格的内容
sheet.addCell(label);
}
}
private WritableCellFormat testStyle(int i) throws WriteException {
WritableFont wfc=null;
if (i==0) {
wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
}else {
wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
}
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
//设置边框为实线
wcfFC.setAlignment(jxl.write.Alignment.CENTRE);
//设置垂直对齐为居中对齐
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
wcfFC.setBorder(jxl.format.Border.TOP, BorderLineStyle.THIN);
//设置右边框线为实线
wcfFC.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.THIN);
//设置底部边框线为实线
wcfFC.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.THIN);
//设置左边框线为实线
wcfFC.setBorder(jxl.format.Border.LEFT, BorderLineStyle.THIN);
if (i==0) {
wcfFC.setBackground(jxl.format.Colour.GRAY_25);//设置单元格的颜色为浅灰色
}
return wcfFC;
}
public List getAllTableName(Connection cnn) throws SQLException{
List tables = new ArrayList();
DatabaseMetaData dbMetaData = cnn.getMetaData();
//可为:"TABLE", "VIEW", "SYSTEM TABLE",
//"GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
String[] types = {"TABLE"};
ResultSet tabs = dbMetaData.getTables(null, null, null,types/*只要表就好了*/);
/*记录集的结构如下:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
TABLE_TYPE String => table type.
REMARKS String => explanatory comment on the table
TYPE_CAT String => the types catalog (may be null)
TYPE_SCHEM String => the types schema (may be null)
TYPE_NAME String => type name (may be null)
SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
*/
while(tabs.next()){
//只要表名这一列
tables.add(tabs.getObject("TABLE_NAME"));
}
System.out.println(tables);
return tables;
}
}
0 0
- 用jxl将mysql的数据结构导入到excel中
- JXL将excel的数据导入到数据库
- mysql将数据导入到excel中
- jxl导入Excel到数据库中!!!
- 用Jxl实现将统计数据导出到excel表中
- java中jxl将数据库中的数据导入到excel中
- java中jxl将excel中的数据导入到数据库中
- 将Excel导入到mysql
- 将Excel导入到mysql
- java jxl利用反射将数据库数据导入到Excel
- 如何将Access和Excel导入到Mysql中之一
- 将 Excel 中的数据导入到 Mysql 中
- 将excel中数据导入到mysql数据库
- 将excel表导入到MySQL数据库表中
- 将Excel表格或者CSV文件导入到Mysql中
- 将Excel中的数据导入到MySql数据库中
- 将mysql数据库中对应的表里面的数据导入到excel中
- 如何将excel表格的数据导入到mysql数据中去
- 解密:几十万Docker容器如何撑起阿里双11
- Java 模拟 HTTP Get Post 请求实现论坛自动回帖
- [源码学习] -- yii2源码学习笔记(三) -- Component代码详解
- 免费的论文查重网站
- MSDN对链接器工具错误 LNK2019的解释
- 用jxl将mysql的数据结构导入到excel中
- 坐标转换
- POJ-3069 Saruman's Army (贪心)
- Java Bean以及xsd相互生成
- C#遍历所有文件夹处理
- 让chrome浏览器支持ajax跨域
- spring——控制反转
- linux系统之_进程及内存管理的前世今生
- 材质颜色