java操作Excel文件

来源：互联网发布：手机淘宝首页大图尺寸编辑：程序博客网时间：2024/06/05 00:57

问题：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 ｂａｓｅ-------------------------");
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 ｂａｓｅ 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();
} ;

}

}

0 0