在java中,如何将数据导出到Excel里去? 100分 如题,数据是从Sqlserver中提取的,谢谢!!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 google搜下 "jxl 导出 excel" 可以找到你要的 没有格式要求的话导出csv就可以了excel可以读的 Java中多Excel的读取有多种方式,这里介绍一种简单的方式给你:jxl。下载jar包后加到classpath就OK了!~~ 你把数据库中的记录读取出来后构造成对象,然后把该对象写入到Excel中即可,代码如下:public class ExcelFactory { public static WritableCellFormat wcf1 = null; public static WritableCellFormat wcf2 = null; public static WritableCellFormat wcf3 = null; public static WritableCellFormat wcf4 = null; public static WritableCellFormat wcf5 = null; public static WritableCellFormat wcf6 = null; public static WritableCellFormat wcf7 = null; public static WritableCellFormat wcf8 = null; public static WritableCellFormat wcf9 = null; private static void GenerageFormat() throws WriteException { WritableFont wf1 = new WritableFont(WritableFont.TIMES, 20, WritableFont.BOLD, false); WritableFont wf2 = new WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false); WritableFont wf3 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false); wcf1 = new WritableCellFormat(wf1); wcf1.setAlignment(Alignment.CENTRE); wcf1.setVerticalAlignment(VerticalAlignment.CENTRE); wcf1.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf2 = new WritableCellFormat(wf2); wcf2.setAlignment(Alignment.RIGHT); wcf2.setVerticalAlignment(VerticalAlignment.CENTRE); wcf2.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf3 = new WritableCellFormat(wf2); wcf3.setAlignment(Alignment.CENTRE); wcf3.setVerticalAlignment(VerticalAlignment.CENTRE); wcf3.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf4 = new WritableCellFormat(wf2); wcf4.setAlignment(Alignment.LEFT); wcf4.setVerticalAlignment(VerticalAlignment.CENTRE); wcf4.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf5 = new WritableCellFormat(wf3); wcf5.setAlignment(Alignment.CENTRE); wcf5.setVerticalAlignment(VerticalAlignment.CENTRE); wcf5.setWrap(true); wcf5.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf6 = new WritableCellFormat(wf3); wcf6.setAlignment(Alignment.CENTRE); wcf6.setVerticalAlignment(VerticalAlignment.CENTRE); wcf6.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf7 = new WritableCellFormat(wf2); wcf7.setAlignment(Alignment.RIGHT); wcf7.setVerticalAlignment(VerticalAlignment.CENTRE); wcf7.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf8 = new WritableCellFormat(wf2); wcf8.setAlignment(Alignment.CENTRE); wcf8.setVerticalAlignment(VerticalAlignment.CENTRE); wcf8.setWrap(true); wcf8.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf9 = new WritableCellFormat(wf2); wcf9.setAlignment(Alignment.LEFT); wcf9.setVerticalAlignment(VerticalAlignment.CENTRE); wcf9.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); } public static void exportMoney()throws Exception{ GenerageFormat(); //获取数据对象 List<Material> materialList = (List<Material>) session.getAttribute("mat"); String path = ServletActionContext.getServletContext().getRealPath("/filetemp"); WritableWorkbook wwb = Workbook.createWorkbook(new File(path + "/exportMoney.xls")); jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 0); // 设置每列的宽度 ws.setColumnView(0, 15); ws.setColumnView(1, 20); ws.setColumnView(2, 20); ws.setColumnView(3, 25); ws.setColumnView(4, 20); ws.setColumnView(5, 20); ws.setColumnView(6, 20); ws.setColumnView(7, 20); String title = "导出数据"; Label labelCell = new Label(0, 0, title, wcf1); ws.addCell(labelCell); ws.mergeCells(0, 0, 7, 0); labelCell = new Label(0, 1, "工程单位", wcf3); ws.addCell(labelCell); labelCell = new Label(1, 1, "SAP项目编号", wcf3); ws.addCell(labelCell); labelCell = new Label(2, 1, "SAP项目名称", wcf3); ws.addCell(labelCell); labelCell = new Label(3, 1, "合同名称", wcf3); ws.addCell(labelCell); labelCell = new Label(4, 1, "预付款(元)", wcf3); ws.addCell(labelCell); labelCell = new Label(5, 1, "货款(元)", wcf3); ws.addCell(labelCell); labelCell = new Label(6, 1, "质保金(元)", wcf3); ws.addCell(labelCell); labelCell = new Label(7, 1, "金额(元)", wcf3); ws.addCell(labelCell); int row = 2; for(Material m : materialList){ labelCell = new Label(0, row, m.getEngineeringUnit(), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(1, row, m.getProjectId(), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(2, row, m.getProjectName(), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(3, row, m.getContractName(), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(4, row, String.valueOf(m.getAdvancePayment()), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(5, row, String.valueOf(m.getPayment()), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(6, row, String.valueOf(m.getDeposit()), ExcelFactory.wcf5); ws.addCell(labelCell); labelCell = new Label(7, row, String.valueOf(m.getMaxPrice()), ExcelFactory.wcf5); ws.addCell(labelCell); row++; } // 写入Exel工作表 wwb.write(); // 关闭Excel工作薄对象 wwb.close(); }} 1 从数据库读取数据2 用jxl生成数据表格一个使用JXL操作Excel的例子 为了简单起见,严重推荐用.CSV格式文件,CSV格式是逗号分隔符文件,用EXCEL可以直接打开,进行各种操纵,CSV格式如下:Sno,Sname,Score0910,xiaopan,800911,aaaa,780912,xxx,5如此,你只需要建的把数据按上面格式用JAVA写到文件,文件的后缀名只需要是CSV就行了,简单 个人觉得如果不要求在 JAVA 里面对导出数据文件的格式进行控制。只是导出数据的话,用 CSV 就好了。相对来说比较简单。 csv格式的没有大于65535行的限制,当excel超过65535行后将不显示超出的行数 答:方法很多.我也提供一种(既不是POI,也不是JXL),你直接用SQL从Sqlserver读,然后直接用SQL的INSERT向EXCEL写就行了(使用:jdbc-odbc驱动). jxl哦也推荐这个,个人认为简单易操作 1.不用程序导的话呢,就用工具导,强烈推荐SQL Developer,它能轻而易举的导出成excel,并且是java写的,oracle公司出品,不用安装oracle客户端;2.用程序导的话呢,建议使用poi,apache的开源项目,个人感觉比jxl要强大。 jxl 不错哈,读csv 是最好了小马过河 关于java socket的问题 byte 的运算问题 有关数组存储的问题 求java主题的用法及一些漂亮的java主题 四連棋遊戲 幫忙一下 谁有好的java学习笔记推荐给我的?谢谢. 请问,这段英文什么意思? 多线程同步问题(管道?) 字符排序算法问题 该段代码的结果是wrong answer,求算法大神 jdbc连接oracle产生ORA-12505 异常 从10万个文件中找出相同的文件
你把数据库中的记录读取出来后构造成对象,然后把该对象写入到Excel中即可,代码如下:
public class ExcelFactory { public static WritableCellFormat wcf1 = null; public static WritableCellFormat wcf2 = null; public static WritableCellFormat wcf3 = null; public static WritableCellFormat wcf4 = null; public static WritableCellFormat wcf5 = null; public static WritableCellFormat wcf6 = null; public static WritableCellFormat wcf7 = null; public static WritableCellFormat wcf8 = null; public static WritableCellFormat wcf9 = null; private static void GenerageFormat() throws WriteException {
WritableFont wf1 = new WritableFont(WritableFont.TIMES, 20,
WritableFont.BOLD, false);
WritableFont wf2 = new WritableFont(WritableFont.TIMES, 12,
WritableFont.NO_BOLD, false);
WritableFont wf3 = new WritableFont(WritableFont.TIMES, 10,
WritableFont.NO_BOLD, false); wcf1 = new WritableCellFormat(wf1);
wcf1.setAlignment(Alignment.CENTRE);
wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf1.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf2 = new WritableCellFormat(wf2);
wcf2.setAlignment(Alignment.RIGHT);
wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf2.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf3 = new WritableCellFormat(wf2);
wcf3.setAlignment(Alignment.CENTRE);
wcf3.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf3.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf4 = new WritableCellFormat(wf2);
wcf4.setAlignment(Alignment.LEFT);
wcf4.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf4.setBorder(jxl.format.Border.NONE, jxl.format.BorderLineStyle.THIN); wcf5 = new WritableCellFormat(wf3);
wcf5.setAlignment(Alignment.CENTRE);
wcf5.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf5.setWrap(true);
wcf5.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf6 = new WritableCellFormat(wf3);
wcf6.setAlignment(Alignment.CENTRE);
wcf6.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf6.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf7 = new WritableCellFormat(wf2);
wcf7.setAlignment(Alignment.RIGHT);
wcf7.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf7.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf8 = new WritableCellFormat(wf2);
wcf8.setAlignment(Alignment.CENTRE);
wcf8.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf8.setWrap(true);
wcf8.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); wcf9 = new WritableCellFormat(wf2);
wcf9.setAlignment(Alignment.LEFT);
wcf9.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf9.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
} public static void exportMoney()throws Exception{
GenerageFormat(); //获取数据对象
List<Material> materialList = (List<Material>) session.getAttribute("mat"); String path = ServletActionContext.getServletContext().getRealPath("/filetemp");
WritableWorkbook wwb = Workbook.createWorkbook(new File(path + "/exportMoney.xls"));
jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 0);
// 设置每列的宽度
ws.setColumnView(0, 15);
ws.setColumnView(1, 20);
ws.setColumnView(2, 20);
ws.setColumnView(3, 25);
ws.setColumnView(4, 20);
ws.setColumnView(5, 20);
ws.setColumnView(6, 20);
ws.setColumnView(7, 20);
String title = "导出数据";
Label labelCell = new Label(0, 0, title, wcf1);
ws.addCell(labelCell);
ws.mergeCells(0, 0, 7, 0);
labelCell = new Label(0, 1, "工程单位", wcf3);
ws.addCell(labelCell);
labelCell = new Label(1, 1, "SAP项目编号", wcf3);
ws.addCell(labelCell);
labelCell = new Label(2, 1, "SAP项目名称", wcf3);
ws.addCell(labelCell);
labelCell = new Label(3, 1, "合同名称", wcf3);
ws.addCell(labelCell);
labelCell = new Label(4, 1, "预付款(元)", wcf3);
ws.addCell(labelCell);
labelCell = new Label(5, 1, "货款(元)", wcf3);
ws.addCell(labelCell);
labelCell = new Label(6, 1, "质保金(元)", wcf3);
ws.addCell(labelCell);
labelCell = new Label(7, 1, "金额(元)", wcf3);
ws.addCell(labelCell); int row = 2;
for(Material m : materialList){
labelCell = new Label(0, row, m.getEngineeringUnit(), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(1, row, m.getProjectId(), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(2, row, m.getProjectName(), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(3, row, m.getContractName(), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(4, row, String.valueOf(m.getAdvancePayment()), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(5, row, String.valueOf(m.getPayment()), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(6, row, String.valueOf(m.getDeposit()), ExcelFactory.wcf5);
ws.addCell(labelCell);
labelCell = new Label(7, row, String.valueOf(m.getMaxPrice()), ExcelFactory.wcf5);
ws.addCell(labelCell);
row++;
}
// 写入Exel工作表
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
}}
2 用jxl生成数据表格一个使用JXL操作Excel的例子
0910,xiaopan,80
0911,aaaa,78
0912,xxx,5如此,你只需要建的把数据按上面格式用JAVA写到文件,文件的后缀名只需要是CSV就行了,简单
2.用程序导的话呢,建议使用poi,apache的开源项目,个人感觉比jxl要强大。