SELECT
IFNULL(COLUMN_NAME,'') AS columnmName,
IFNULL(COLUMN_TYPE,'') AS columnType,
IFNULL(DATA_TYPE ,'')AS dataType,
IFNULL(CHARACTER_MAXIMUM_LENGTH,'') AS length,
IFNULL(IS_NULLABLE,'') AS isNullable,
IFNULL(COLUMN_DEFAULT,'') AS columnDefault,
IFNULL(COLUMN_COMMENT,'') AS columnComment
FROM
INFORMATION_SCHEMA. COLUMNS
WHERE
-- fangwifi_new 为数据库名称,fangwifi_advance 表名,到时候只需要修改成你要导出表结构的数据库即可
table_schema = 'fangwifi_new'
AND table_name = 'fangwifi_advance'
查看一个数据库有多少个表:
SELECT
table_name as tableName
FROM
information_schema. TABLES
WHERE
table_schema = 'fangwifi_new'
废话不多说,上代码:
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.fangwifi.dao.user.UserDao;
import com.fangwifi.domain.user.Table;
public class PoiTest {
public static void main(String[] args) {
try {
FileOutputStream out = new FileOutputStream("c:\\房微爱.xls");
ExportExcelUtils eeu = new ExportExcelUtils();
HSSFWorkbook workbook = new HSSFWorkbook();
String[] headers = {"序号","列名","数据类型","字段类型", "长度","是否为空","默认值" ,"备注 "};
ClassPathXmlApplicationContext context = new
ClassPathXmlApplicationContext("/spring-config.xml");
UserDao userDao= (UserDao) context.getBean("userDao");
List<Table> allTables = userDao.listTablesBySchema(new Table());
for( int k=0; k<allTables.size();k++){
Table table = allTables.get(k);
Table tableQuery = new Table();
tableQuery.setSchema("fangwifi_new");
tableQuery.setTableName(table.getTableName());
List<Table> list = userDao.listTablesByTable(tableQuery);
List<List<String>> data = new ArrayList<List<String>>();
for(int i=0; i < list.size();i++){
Table t = list.get(i);
List rowData = new ArrayList();
rowData.add(String.valueOf(i+1));
rowData.add(t.getColumnmName());
rowData.add(t.getColumnType());
rowData.add(t.getDataType());
rowData.add(t.getLength());
rowData.add(t.getIsNullable());
rowData.add(t.getColumnDefault());
rowData.add(t.getColumnComment());
data.add(rowData);
}
eeu.exportExcel(workbook, k, tableQuery.getTableName(), headers, data, out);
}
//原理就是将所有的数据一起写入,然后再关闭输入流。
workbook.write(out);
System.out.println("导出完成");
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class ExportExcelUtils {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2014-01-09
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List<String>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<String> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (String str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
}