`
- 浏览:
37538 次
- 性别:
- 来自:
北京
-
package com.unisys;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
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;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
public class Excels {
/**
* 到处Excel文件的Action
* @return
* @throws Exception
* @throws Exception
* @throws Exception
*/
public static void main(String[] args) throws Exception {
List userList = findDate();
// 调用导出方法
try {
export(userList);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 导出操作
* @param col 所要导出对象集合
*/
private static void export(List<String[]> col) throws Exception {
FileOutputStream out = new FileOutputStream("d://"+"导出excel数据"+System.currentTimeMillis() + ".xls");
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(getExcelInputStream(col));
bos = new BufferedOutputStream(out);
byte[] buff = new byte[4096];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
private static InputStream getExcelInputStream(List col) throws Exception, NoSuchMethodException, IllegalAccessException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet=null;
HSSFCellStyle titleStyle=null;
Short count = 2;
int flag=0;
int tiao=700;
//判断几个sheet表
if(col.size()%tiao==0){
flag=col.size()/tiao;
}else{
flag=col.size()/tiao+1;
}
int j=1;
//控制表的张数
for (int i = 0; i < flag; i++) {
sheet = wb.createSheet("sheet"+(i+1));
titleStyle = wb.createCellStyle();
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short);
font.setItalic(false);
font.setStrikeout(false);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
//中间对齐的格式
HSSFCellStyle centerStyle = wb.createCellStyle();
centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//右边对齐的格式
HSSFCellStyle rightStyle = wb.createCellStyle();
rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//为sheet生成标题Row的所有类容
processExcelHeader(sheet, titleStyle);
//处理每个列的宽度
processCellWidth(sheet);
//控制每张表的条数
for (;j < col.size()+1; j++) {
if(j%tiao==0){
processElectricitybillBasestationToExcel((String[])col.get(j-1), count, sheet, centerStyle, rightStyle);
count = (short) (count + 1);
count=2;
break;
}
processElectricitybillBasestationToExcel((String[])col.get(j-1), count, sheet, centerStyle, rightStyle);
count = (short) (count + 1);
}
j++;
}
count = (short) (count + 2);
/* for (Iterator ite = col.iterator(); ite.hasNext();) {
//将信息写入Excel中
processElectricitybillBasestationToExcel((StuModel) ite.next(), count, sheet, centerStyle, rightStyle);
count = (short) (count + 1);
}*/
//合计
HSSFRow row = sheet.createRow(count);
//第一行的单元格
HSSFCell cell = null;
cell = row.createCell((short) 0);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("数据总计:"+col.size());
cell.setCellStyle(titleStyle);
cell = row.createCell((short) 1);
sheet.addMergedRegion(new Region(count, (short) 0, count, (short) 7));
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
return is;
}
/**
* 处理Excel的标题栏
* @param sheet
* @param titleStyle
*/
private static void processExcelHeader(HSSFSheet sheet, HSSFCellStyle titleStyle) {
//行号
//第一行
HSSFRow row = sheet.createRow(0);
//第二行
HSSFRow sRow = sheet.createRow(1);
//第一行的单元格
HSSFCell cell = null;
//第二行的单元格
HSSFCell sCell = null;
//序号
cell = row.createCell((short) 0);
cell.setCellStyle(titleStyle);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(titleStyle);
cell.setCellValue("序号");
sCell = sRow.createCell((short) 0);
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));
//clcustid
cell = row.createCell((short) 1);
cell.setCellStyle(titleStyle);
cell.setCellValue("CLCUSTID");
sCell = sRow.createCell((short) 1);
sheet.addMergedRegion(new Region(0, (short) 1, 1, (short) 1));
cell.setCellStyle(titleStyle);
//csrname
cell = row.createCell((short) 2);
cell.setCellStyle(titleStyle);
cell.setCellValue("custname");
sCell = sRow.createCell((short) 2);
sheet.addMergedRegion(new Region(0, (short) 2, 1, (short) 2));
//clcallid
cell = row.createCell((short) 3);
cell.setCellStyle(titleStyle);
cell.setCellValue("CLCALLID");
sCell = sRow.createCell((short) 3);
sheet.addMergedRegion(new Region(0, (short) 3, 1, (short) 3));
//clcalltime
cell = row.createCell((short) 4);
cell.setCellStyle(titleStyle);
cell.setCellValue("CLCALLTIME");
sCell = sRow.createCell((short) 4);
sheet.addMergedRegion(new Region(0, (short) 4, 1, (short) 4));
//clcsrid
cell = row.createCell((short) 5);
cell.setCellStyle(titleStyle);
cell.setCellValue("CLCSRID");
sCell = sRow.createCell((short) 5);
sheet.addMergedRegion(new Region(0, (short) 5, 1, (short) 5));
//clclitelno
cell = row.createCell((short) 6);
cell.setCellStyle(titleStyle);
cell.setCellValue("CLCLITELNO");
sCell = sRow.createCell((short) 6);
sheet.addMergedRegion(new Region(0, (short) 6, 1, (short) 6));
//callreasontype
cell = row.createCell((short) 7);
cell.setCellStyle(titleStyle);
cell.setCellValue("CALLREASONTYPE");
sCell = sRow.createCell((short) 7);
sheet.addMergedRegion(new Region(0, (short) 7, 1, (short) 7));
//callreason
cell = row.createCell((short);
cell.setCellStyle(titleStyle);
cell.setCellValue("CALLREASON");
sCell = sRow.createCell((short);
sheet.addMergedRegion(new Region(0, (short) 8, 1, (short));
}
/**
* 全局控制 多少列
* @param sheet
*/
private static void processCellWidth(HSSFSheet sheet) {
sheet.setColumnWidth((short) 0, (short) (10 * 256));
sheet.setColumnWidth((short) 1, (short) (15 * 256));
sheet.setColumnWidth((short) 2, (short) (15 * 256));
sheet.setColumnWidth((short) 3, (short) (20 * 256));
sheet.setColumnWidth((short) 4, (short) (20 * 256));
sheet.setColumnWidth((short) 5, (short) (20 * 256));
sheet.setColumnWidth((short) 6, (short) (15 * 256));
sheet.setColumnWidth((short) 7, (short) (15 * 256));
sheet.setColumnWidth((short) 8, (short) (15 * 256));
}
/**
* 将 要导出的数据 的信息填充到Excel中
* @param eb
* @param count
* @param sheet
* @param centerStyle
* @throws Exception
*/
private static void processElectricitybillBasestationToExcel(String[] str, Short count, HSSFSheet sheet, HSSFCellStyle centerStyle, HSSFCellStyle rightStyle) throws Exception {
//根据基站编号取得基站信息
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
//行号
//第一行
HSSFRow row = sheet.createRow(count);
//第一行的单元格
HSSFCell cell = null;
//序号
cell = row.createCell((short) 0);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(count - 1);
cell.setCellStyle(centerStyle);
//车号
cell = row.createCell((short) 1);
cell.setCellStyle(centerStyle);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(str[1]);
//车辆型号
cell = row.createCell((short) 2);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(centerStyle);
cell.setCellValue(str[0]);
}
public static List<String[]> findDate() throws Exception
{
List<String[]> list=new ArrayList<String[]>();
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url, "sl", "sl");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String sql="select * from student";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs= ps.executeQuery();
while(rs.next()){
String[] s=new String[2];
s[0]=rs.getString("stuId");
s[1]=rs.getString("stuName");
list.add(s);
}
ps.execute();
ps.close();
conn.close();
return list;
}
}
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
java连接数据库登录 查询结果导出excel,用的是简单的servlet技术
java 数据库 导出 excel
java实现mysql数据库的表导出到excel文件,已经基本的demo包含jar包,可以导入的eclipse里面。比较好用吧
Java实现数据库数据导出到excel,内有数据连接jar包,完整案例
1、java解析读取excel文件中的数据,并写入数据库。 2、java读取数据库数据,并导出为excel文件。 3、README.md中有详细的操作步骤示例。 使用说明: 1. 先使用postman导入:other/excel相关.postman_collection....
JAVA实现数据库数据导入导出到Excel(POI)所需jar包
使用poi从数据库导出excel表的示例
通过条件查询数据库list,根据list去导出多列的excel表格,亲测有效工具类和代码js controller都放在一起
jsp+Servlet实现的最简单的Excel表导入导出数据库的操作
1.java连接mongo数据库查询统计信息导出为excel表格 2.所有连接参数都可以动态输入,参数包括:ip、端口、数据库名称、集合名称、查询参数(公众号,写在指定文件中),导出表格存放位置
Java实现excel文档数据导入数据库、导出excel
数据库表数据导出Excel(支持mysql,oralce),支持百万数据导出,自动分页,每页导出多少条数据可以从配置文件配置,建议80w以下
使用Hibernate操作数据库,将数据库的表数据导出到Excel文件中
SpringBootMybatis+poi+Thymeleaf实现excel文件数据导入到数据库以及从数据库将数据导出成excel
Java中的一些需要用到的导入包,
支持从数据库中查询到数据,以多级表头形式导入到Excel文件中。比如:一级表头:学号、姓名、各科成绩。二级表头:语文、数学、英语(二级表头在各科成绩下面)。
根据excel模板动态导出数据库数据 package text; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import javax.servlet....
获取通话记录文件格式vcf的转化,JAVA实现数据库数据导入/导出到Excel(POI)
该资源是一个rar压缩包形式的源代码,主要演示了java如何导出数据到Excle并保存到本地电脑、以及在web中如何导出数据库中的数据到Excel并下载。