在java中如何把sqlserver中的数据导出到excel
在java中如何把sqlserver中的数据导出到excel呢???数据库用的是2005 --------------------编程问答-------------------- 把数据读取出来,存到list中,遍历list,使用poi写入到一个excel文档中,然后把文档保存就可以了。 --------------------编程问答-------------------- LZ 代码贴给你瞧瞧 ,是公共的导出Excel方法哦 记得结贴哦1. 导出Excel辅助类
public class util2 {
public static void excel(HttpServletResponse response, List list,
String[] firstLine, String sheetName, String fileName) {
String[] array1 = null;
try {
//格式化时间
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String nowDate=sdf.format(new Date());
String filename=fileName+nowDate+".xls";
short i = 0;// row行标
response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
response.setHeader("Content-Disposition", "filename="+ new String(filename.getBytes("gb2312"), "iso8859-1"));//设置文件头编码方式和文件名
HSSFWorkbook wb = new HSSFWorkbook();//excel文件,一个excel文件包含多个表
HSSFSheet sheet = wb.createSheet();//表,一个表包含多个行
wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);// 设置sheet中文编码;
//设置字体等样式
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("Courier New");
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setWrapText(true);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFRow row;//行,一行包括多个单元格
HSSFCell cell;//单元格
row = sheet.createRow(i);//由HSSFSheet生成行
row.setHeightInPoints((float) 30);
//生成首行
for (short j = 0; j < firstLine.length; j++) {
cell = row.createCell(j);//由行生成单元格
cell.setCellStyle(style);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(firstLine[j]);
sheet.setColumnWidth(j, (short) (5000));
}
//生成所有行的单元格内容,如果测试list设为null即可,或者将这一段代码注释掉
if (null == list || list.size() == 0) {
// do nothing
} else {
for (int k = 0; k < list.size(); k++) {
row = sheet.createRow(++i);
row.setHeightInPoints((float) 20);
array1 = (String[]) list.get(k);
if (null != array1 && array1.length != 0) {
for (int f = 0; f < array1.length; f++) {
cell = row.createCell((short) f);
cell.setCellStyle(style);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
System.out.println("==========="+array1[f]);
cell.setCellValue(array1[f]);
}
}
// row = sheet.createRow((int)++i);
// Student stu = (Student) list.get(k);
// //第四步,创建单元格,并设置值
// cell = row.createCell((short) k);
// cell.setCellStyle(style);
// row.createCell((short)0).setCellValue((double)stu.getId());
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// row.createCell((short)1).setCellValue(stu.getName());
// row.createCell((short)2).setCellValue((double)stu.getAge());
// row.createCell((short)3).setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBrith()));
}
}
//输出
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return;
}
}
2.测试类 Test.java
String sql="select id,stuName,stuAddress,studate from saa.student ";
List list=lvyouservice.userList(sql);
System.out.println("========查询出来的结果"+list.size());
//调用导出excel方法
List lis=new ArrayList();
String s1 [] =null;
for(int i=0;i<list.size();i++){
Map to=(Map)list.get(i);
System.out.println(to+"========");
s1=new String []{to.get("id")==null ? "" :to.get("id").toString(),to.get("stuname")==null?"":to.get("stuname").toString(),to.get("stuaddress")==null?"":to.get("stuaddress").toString()};
lis.add(s1);
}
String ss[]=new String[]{"学员Id","学员姓名","学员地址"};
util2.excel(response, lis, ss, "是吗", "学员考勤记录");
--------------------编程问答--------------------
我把代码整理一下
--------------------编程问答-------------------- 把下面的一段内容另存为一个 xls 文件,再用 excel 软件打开,也是一个 excel 表格。
public class util2 {
public static void excel(HttpServletResponse response, List list,
String[] firstLine, String sheetName, String fileName) {
String[] array1 = null;
try {
//格式化时间
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String nowDate=sdf.format(new Date());
String filename=fileName+nowDate+".xls";
short i = 0;// row行标
response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
response.setHeader("Content-Disposition", "filename="+ new String(filename.getBytes("gb2312"), "iso8859-1"));//设置文件头编码方式和文件名
HSSFWorkbook wb = new HSSFWorkbook();//excel文件,一个excel文件包含多个表
HSSFSheet sheet = wb.createSheet();//表,一个表包含多个行
wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);// 设置sheet中文编码;
//设置字体等样式
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("Courier New");
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setWrapText(true);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFRow row;//行,一行包括多个单元格
HSSFCell cell;//单元格
row = sheet.createRow(i);//由HSSFSheet生成行
row.setHeightInPoints((float) 30);
//生成首行
for (short j = 0; j < firstLine.length; j++) {
cell = row.createCell(j);//由行生成单元格
cell.setCellStyle(style);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell中文编码;
cell.setCellValue(firstLine[j]);
sheet.setColumnWidth(j, (short) (5000));
}
//生成所有行的单元格内容,如果测试list设为null即可,或者将这一段代码注释掉
if (null == list || list.size() == 0) {
// do nothing
} else {
for (int k = 0; k < list.size(); k++) {
row = sheet.createRow(++i);
row.setHeightInPoints((float) 20);
array1 = (String[]) list.get(k);
if (null != array1 && array1.length != 0) {
for (int f = 0; f < array1.length; f++) {
cell = row.createCell((short) f);
cell.setCellStyle(style);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
System.out.println("==========="+array1[f]);
cell.setCellValue(array1[f]);
}
}
// row = sheet.createRow((int)++i);
// Student stu = (Student) list.get(k);
// //第四步,创建单元格,并设置值
// cell = row.createCell((short) k);
// cell.setCellStyle(style);
// row.createCell((short)0).setCellValue((double)stu.getId());
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// row.createCell((short)1).setCellValue(stu.getName());
// row.createCell((short)2).setCellValue((double)stu.getAge());
// row.createCell((short)3).setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBrith()));
}
}
//输出
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return;
}
}
2.测试类 Test.java
String sql="select id,stuName,stuAddress,studate from saa.student ";
List list=lvyouservice.userList(sql);
System.out.println("========查询出来的结果"+list.size());
//调用导出excel方法
List lis=new ArrayList();
String s1 [] =null;
for(int i=0;i<list.size();i++){
Map to=(Map)list.get(i);
System.out.println(to+"========");
s1=new String []{to.get("id")==null ? "" :to.get("id").toString(),to.get("stuname")==null?"":to.get("stuname").toString(),to.get("stuaddress")==null?"":to.get("stuaddress").toString()};
lis.add(s1);
}
String ss[]=new String[]{"学员Id","学员姓名","学员地址"};
util2.excel(response, lis, ss, "是吗", "学员考勤记录");
<table style="border-collapse:collapse" border="1" bordercolor="black">
<tr>
<td>姓名</td><td>性别</td><td>年龄</td>
</tr>
<tr>
<td>张三</td><td>男</td><td>14</td>
</tr>
</table>
所以,只要会写 jsp,懂点儿 html 就没问题
这个跟用什么数据库没联系的
补充:Java , Web 开发