问题:Excel文件处理,将多列转换为多行,想法为将Excel表中的数据读取到Mysql数据中,再读出到一个新的Excel表中。
如:
处理后为:
1、将数据写入得到数据库:
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
public class PayslipExcelImport {
static String createTableSql = "";// 创建数据库的sql
static String colType = "TEXT";// 字段类型
static String key = "id";// 主键
static String charSet = "utf8";// 表格字符类型
static String ENGINE = "InnoDB";// 表格类型
static String tableName = "tempExcelToMysql";// 表名称
static String colName = "col";// 默认字段名
static Connection conn = null;
public static void main(String args[]) {
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
System.out.println("start load file-------------------------");
InputStream is = new FileInputStream("lib/a2.xls");// 创建输入
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); // 读取第一个sheet
int colNum = rs.getColumns();// 列数
int rowNum = rs.getRows();// 行数
System.out.println("colNum rowNum------------------" + rowNum + ","
+ colNum);
System.out.println("start create base-------------------------");
getConntion();
String tableSql = getCreateTableSql(rowNum, colNum);
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
st.execute(tableSql);
st.close();
System.out.println("create base end -------------------------");
String sql = getColName(rowNum, colNum);
PreparedStatement ps = null;
String strValue = "";
ps = conn.prepareStatement(sql);
for (int i = 1; i < rowNum; i++) {
strValue = "";
for (int j = 0; j < colNum; j++) {
Cell c = rs.getCell(j, i);
strValue = c.getContents();
ps.setString(j + 1, strValue);
}
ps.addBatch();
}
ps.executeBatch();
conn.commit();
if (ps != null) {
ps.close();
}
System.out.println(" insert end-------------------------");
close();
} catch (Exception e) {
e.printStackTrace();
}
}
static String getCreateTableSql(int rowNum, int colNum) {
// 可以做成可配置文件
createTableSql = "create table " + tableName + "( `" + key
+ "` bigint(12) NOT NULL auto_increment, ";
String temp = "";
for (int j = 0; j < colNum; j++) {
temp = temp + "`" + colName + j + "` " + colType + " DEFAULT NULL,";
}
createTableSql = createTableSql + " " + temp + " PRIMARY KEY (`" + key
+ "`)" + ") ENGINE=" + ENGINE + " DEFAULT CHARSET=" + charSet
+ ";";
return createTableSql;
}
static String getColName(int rowNum, int colNum) {
// 可以做成可配置文件
String colSql = "";
String colValue = "";
for (int j = 0; j < colNum; j++) {
colSql = colSql + "`" + colName + j + "`,";
colValue = colValue + "" + "?,";
}
return "insert into " + tableName + " ("
+ colSql.substring(0, colSql.lastIndexOf(",")) + ")values("
+ colValue.substring(0, colValue.lastIndexOf(",")) + ")";
}
static void getConntion() {
try {
String driver_class = "com.mysql.jdbc.Driver";
String connection_url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
String user_name = "root";
String db_password = "123456";
Class.forName(driver_class);
conn = DriverManager.getConnection(connection_url, user_name,
db_password);
conn.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
}
static void close() {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2、从数据库读出到新的Excel表,同时进行数据格式转换处理:
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
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 readEx {
public static void main(String[] args) throws IOException {
readEx re = new readEx();
re.Ex();
}
public void Ex() throws IOException{
DbUtil_budget dbb = new DbUtil_budget();
String sql_col = "select COLUMN_NAME from information_schema.COLUMNS where table_name = 'tempExcelToMysql';";
List<Map<String, String>> list_col = dbb.queryToList(sql_col);
System.out.println("======="+list_col.size());
String sql ="select * from tempExcelToMysql ";
List<Map<String, String>> list = dbb.queryToList(sql);
HSSFWorkbook wb = new HSSFWorkbook();
final HSSFSheet sheet = wb.createSheet("sheet1");
int size =0;
for (int j = 0; j < list.size(); j++) {
//int flag=0;
for (int i = 0; i < 6; i++) {
HSSFRow row = sheet.createRow(size);
HSSFCell headerCell = row.createCell(0);
headerCell.setCellValue(list.get(j).get("col0"));//设置第一列
HSSFCell headerCell1 = row.createCell(1);
headerCell1.setCellValue(list_col.get(i+2).get("COLUMN_NAME")); //设置第二列
// System.out.println("列名:==="+);
HSSFCell headerCell2 = row.createCell(2);
headerCell2.setCellValue(list.get(j).get("col"+(i+1)));//设置第三列
size ++;
}
}
OutputStream out;
try {
out = new FileOutputStream("E://a.xls");//自己定义存放新的Excel文件的位置
wb.write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} ;
}
}