关闭

Excel生成的例子!

标签: excelimportstringhashmapsqlservermicrosoft
782人阅读 评论(0) 收藏 举报
分类:

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExpExcel {
private final static SimpleDateFormat FORMAT = new SimpleDateFormat(
"yyyy-MM-dd hh:mm:ss");

public static void main(String[] args) {
System.out.println("start time:" + FORMAT.format(new Date()));
genericExcel(true);
System.out.println("end   time:" + FORMAT.format(new Date()));
}

/**
 * @param haveTitle
 *            是否导出表头
 */
public static void genericExcel(boolean haveTitle) {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
.newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=User4Every";

String user = "sa";
String password = "sa";
Connection conn = java.sql.DriverManager.getConnection(url, user,
password);
String fileName = "E://report.xls";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from TPJBjbxx");

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
FileOutputStream fileOut = new FileOutputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(0 + "");
wb.setSheetName(0, "报表1", (short) 1);
HSSFCellStyle cs = wb.createCellStyle(); // 格式对象
HSSFFont fCol = wb.createFont(); // 字体对象,表头
fCol.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFRow row = null;
HSSFCell cell = null;
int nrow = 0;
String s_colType;
if (haveTitle) {
row = sheet.createRow((short) nrow);
for (int i = 0; i < columnCount; i++) {
cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cs.setFont(fCol);
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(cs);
cell.setCellValue(rsmd.getColumnName(i + 1));
}
nrow++;
}
while (rs.next()) {
row = sheet.createRow((short) nrow);
for (int i = 0; i < columnCount; i++) {
s_colType = rsmd.getColumnTypeName(i + 1);
cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 根据字段的类型设置单元格的值
if (s_colType.compareTo("int") == 0) {
cell.setCellValue(rs.getInt(i + 1));
} else if (s_colType.compareTo("decimal") == 0) {
cell.setCellValue(rs.getDouble(i + 1));
} else {
// 除了以上的几种数据类型均以String型对待
cell.setCellValue(rs.getObject(i + 1) + "");
}
}
nrow++;
}
rs.close();
stmt.close();
conn.close();
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}

/**
 * 将ResultSet类型转换为ArrayList类型
 * @param rs
 * @return ArrayList
 * @throws SQLException
 */
public static ArrayList rsToArrayList(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList rows = new ArrayList();
while (rs.next()) {
HashMap row = new HashMap();
for (int i = 1; i <= columnCount; i++) {
String name = rsmd.getColumnName(i);
row.put(name, rs.getObject(i));
}
rows.add(row);
}
return rows;
}
}

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:121469次
    • 积分:1146
    • 等级:
    • 排名:千里之外
    • 原创:30篇
    • 转载:13篇
    • 译文:0篇
    • 评论:30条
    最新评论
    友情链接