package com.sinosig.gis.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
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.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* POI工具类
*
* @author sf
*
* 2016年8月25日 上午10:00:17
*/
public class POIUtil {
/**
*
* @param excelFilePath
* excel文件路径
* @param sheetName
* 第一个工作表名称
* @param cols
* 对导出的excel的列进行命名的数组
* @param sql
* JDBC执行的sql查询语句
* @throws Exception
*/
public static void toExcel_xlsx(String excelFilePath, String sheetName, String[] cols, String sql) throws Exception {
Connection con = null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
con = JdbcUtil.getConnection();
// 对于大表，使用setFetchSize进行每次多行读取
ps = (PreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = ps.executeQuery();
File excelFile = new File(excelFilePath);
if (!excelFile.exists()) {
excelFile.getParentFile().mkdirs();
}
// 创建输出Excel
Workbook book = new XSSFWorkbook();
// 创建工作sheet
Sheet sheet = book.createSheet(sheetName);
//设置单元格的宽度
sheet.setColumnWidth(0, 100 * 50);
sheet.setColumnWidth(1, 100 * 50);
sheet.setColumnWidth(2, 100 * 25);
sheet.setColumnWidth(3, 100 * 50);
//把单元格格式都设置为文本格式★★★★
XSSFCellStyle style = (XSSFCellStyle) book.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) book.createDataFormat();
style.setDataFormat(format.getFormat("@"));
// 使用给定的数组创建excel的第一行
Row row = sheet.createRow(0);
for (int i = 0; i < cols.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(cols[i]);
}
int j = 0;
while (rs.next()) {
j++;
row = sheet.createRow(j);
for (int i = 0; i < cols.length; i++) {
Cell cell = row.createCell(i);
//把单元格格式都设置为文本格式★★★★★
cell.setCellStyle(style);
cell.setCellValue(rs.getString(i + 1));
}
}
FileOutputStream os = new FileOutputStream(excelFile);
book.write(os);
System.out.println("ok....");
JdbcUtil.closeResource(con, st, rs);
}
public static void toExcel_xlsx(String excelFilePath, String sheetName, int cols, String sql) throws Exception {
Connection con = null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
con = JdbcUtil.getConnection();
// 对于大表，使用setFetchSize进行每次多行读取
ps = (PreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = ps.executeQuery();
File excelFile = new File(excelFilePath);
if (!excelFile.exists()) {
excelFile.getParentFile().mkdirs();
}
// 创建输出Excel
Workbook book = new XSSFWorkbook();
// 创建工作sheet
Sheet sheet = book.createSheet(sheetName);
//把单元格格式都设置为文本格式★★★★★
XSSFCellStyle style = (XSSFCellStyle) book.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) book.createDataFormat();
style.setDataFormat(format.getFormat("@"));
int j = 0;
while (rs.next()) {
Row row = sheet.createRow(j);
for (int i = 0; i < cols; i++) {
Cell cell = row.createCell(i);
//把单元格格式都设置为文本格式★★★★★
cell.setCellStyle(style);
cell.setCellValue(rs.getString(i + 1));
}
j++;
}
FileOutputStream os = new FileOutputStream(excelFile);
book.write(os);
System.out.println("ok....");
JdbcUtil.closeResource(con, st, rs);
}
/**
*
* @param excelFilePath
* @param tableName
* 使用excel的第一行数据进行建表
* @throws Exception
*/
public static void fromExcel_xlsx(String excelFilePath, String sheetName, String tableName) throws Exception {
Connection conn = JdbcUtil.getConnection();
Statement st = conn.createStatement();
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath));
// 获取sheet对象(需要导入的sheet名字)
XSSFSheet sheet = workbook.getSheet(sheetName);
// 获取所有行
int rowSize = sheet.getPhysicalNumberOfRows();
// 遍历行
for (int i = 0; i < rowSize; i++) {
// 获取列对象
Row row = sheet.getRow(i);
// 获取每行的所有列
int cellSize = row.getPhysicalNumberOfCells();
String value = "";
// 遍历列
for (int j = 0; j < cellSize; j++) {
Cell cell = row.getCell(j);// 获取单元格对象
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case Cell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
String[] val = value.split(",");
// 使用第一行语句作为建表语句
if (i == 0) {
String sql = "create table " + tableName + "(";
for (int k = 0; k < val.length; k++) {
if (k == val.length - 1) {
sql += val[k] + " varchar(255))";
} else {
sql += val[k] + " varchar(255),";
}
}
st.executeUpdate(sql);
} else {
// 非第一行作为数据插入到表中
String sql = "insert into " + tableName + " values('";
for (int k = 0; k < val.length; k++) {
if (k == val.length - 1) {
sql += val[k] + "')";
} else {
sql += val[k] + "','";
}
}
System.out.println(sql);
st.executeUpdate(sql);
}
}
JdbcUtil.closeResource(conn, st);
}
/**
*
* @param excelFilePath
* @param sheetName
* @param tableName
* @param cols
* 使用给定的列名数组建表
* @throws Exception
*/
public static void fromExcel_xlsx(String excelFilePath, String sheetName, String tableName, String[] cols)
throws Exception {
Connection conn = JdbcUtil.getConnection();
Statement st = conn.createStatement();
// 使用给定的列名数组建表
///////////////////////////////////////////////////////// 建表
String sql = "create table " + tableName + "(";
for (int k = 0; k < cols.length; k++) {
if (k == cols.length - 1) {
sql += cols[k] + " varchar(255))";
} else {
sql += cols[k] + " varchar(255),";
}
}
System.out.println(sql);
st.executeUpdate(sql);
////////////////////////////////////////////////////////////
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath));
// 获取sheet对象(需要导入的sheet名字)
XSSFSheet sheet = workbook.getSheet(sheetName);
// 获取所有行
int rowSize = sheet.getPhysicalNumberOfRows();
// 遍历行
for (int i = 0; i < rowSize; i++) {
// 获取列对象
Row row = sheet.getRow(i);
// 获取每行的所有列
int cellSize = row.getPhysicalNumberOfCells();
String value = "";
// 遍历列
for (int j = 0; j < cellSize; j++) {
Cell cell = row.getCell(j);// 获取单元格对象
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case Cell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
String[] val = value.split(",");
sql = "insert into " + tableName + " values('";
for (int k = 0; k < val.length; k++) {
if (k == val.length - 1) {
sql += val[k] + "')";
} else {
sql += val[k] + "','";
}
}
System.out.println(sql);
st.executeUpdate(sql);
}
JdbcUtil.closeResource(conn, st);
}
}