guojg的专栏

---------乐观、快乐每一天!

Excel生成的例子!

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;
}
}

阅读更多
个人分类: 技术天地
想对作者说点什么? 我来说一句

C# 导出 EXCEL 的实例。

2016年06月17日 23KB 下载

phpexcel 读取 excel实例

2015年03月10日 890KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭