- public void exportResultInfo(String fileName,String savePath) throws Exception{
- HSSFWorkbook book = new HSSFWorkbook();
- String[] greenUrlLabel = new String[] { "A", "B", "C",
- "D", "E", "F" };
- createSheet2(book, "result_info", greenUrlLabel, dataList);
- downLoad(book, fileName, savePath);
- }
public void exportResultInfo(String fileName,String savePath) throws Exception{
HSSFWorkbook book = new HSSFWorkbook();
String[] greenUrlLabel = new String[] { "A", "B", "C",
"D", "E", "F" };
createSheet2(book, "result_info", greenUrlLabel, dataList);
downLoad(book, fileName, savePath);
}
部分代码解释: dataList是从数据中获取到的数据,也就是你想导出的数据
createSheet2(book, "result_info", greenUrlLabel, dataList);将数据写到book中
downLoad(book, fileName, savePath);将book写到excel中,对应的方法分别如下。
将数据写到book中:
- public void createSheet2(HSSFWorkbook book, String sheetName,
- String[] label, List<Object[]> list) {
- // 单sheet 最大行数
- // final Integer MAX_ROW_SIZE = 65536;
- final Integer MAX_ROW_SIZE = 65535;
- // 创建Sheet并设置sheetName
- HSSFSheet sheet = null;
- // sheet number
- int sheetNum = 0;
- // sheet data row
- int rowCount = 0;
- sheet = book.createSheet(sheetName);
- sheetNum++;
- // 首行填写表头
- HSSFRow row00 = sheet.createRow(rowCount++);// 创建行 rowCount+1
- for (int t = 0; t < label.length; t++) {
- HSSFCell cell0 = row00.createCell(t);
- cell0.setCellValue(label[t]);
- }
- // 数据行遍历
- if (list != null && list.size() > 0) {
- for (int i = 1; i <= list.size(); i++) {
- // rowCount 为0时 新sheet初始化、表头数据初始化
- if (rowCount == 0) {
- // 创建新Sheet
- sheet = book.createSheet(sheetName + "_" + sheetNum);
- sheetNum++;
- // 首行填写表头
- HSSFRow row0 = sheet.createRow(rowCount++);// 创建行
- // rowCount+1
- for (int t = 0; t < label.length; t++) {
- HSSFCell cell0 = row0.createCell(t);
- cell0.setCellValue(label[t]);
- }
- }
- // 创建数据行
- HSSFRow row = sheet.createRow(rowCount++); // 第二行开始
- Object[] objects = list.get(i - 1); // 填写数据集合的第一行
- // 列循环
- if (objects != null && objects.length > 0) {
- for (int j = 1; j <= objects.length; j++) {
- // 创建Cell
- HSSFCell cell = row.createCell(j - 1);
- // 获取数据对象
- Object obj = objects[j - 1];
- // 填写数据
- cell.setCellValue(new HSSFRichTextString(
- getStringValueFromObject(obj)));
- }
- }
- // rowcount 归零判断 归零 新Sheet页面的开始
- if (rowCount == MAX_ROW_SIZE) {
- rowCount = 0;
- }
- Contants.expCount++;
- }
- }
- }
public void createSheet2(HSSFWorkbook book, String sheetName,
String[] label, List<Object[]> list) {
// 单sheet 最大行数
// final Integer MAX_ROW_SIZE = 65536;
final Integer MAX_ROW_SIZE = 65535;
// 创建Sheet并设置sheetName
HSSFSheet sheet = null;
// sheet number
int sheetNum = 0;
// sheet data row
int rowCount = 0;
sheet = book.createSheet(sheetName);
sheetNum++;
// 首行填写表头
HSSFRow row00 = sheet.createRow(rowCount++);// 创建行 rowCount+1
for (int t = 0; t < label.length; t++) {
HSSFCell cell0 = row00.createCell(t);
cell0.setCellValue(label[t]);
}
// 数据行遍历
if (list != null && list.size() > 0) {
for (int i = 1; i <= list.size(); i++) {
// rowCount 为0时 新sheet初始化、表头数据初始化
if (rowCount == 0) {
// 创建新Sheet
sheet = book.createSheet(sheetName + "_" + sheetNum);
sheetNum++;
// 首行填写表头
HSSFRow row0 = sheet.createRow(rowCount++);// 创建行
// rowCount+1
for (int t = 0; t < label.length; t++) {
HSSFCell cell0 = row0.createCell(t);
cell0.setCellValue(label[t]);
}
}
// 创建数据行
HSSFRow row = sheet.createRow(rowCount++); // 第二行开始
Object[] objects = list.get(i - 1); // 填写数据集合的第一行
// 列循环
if (objects != null && objects.length > 0) {
for (int j = 1; j <= objects.length; j++) {
// 创建Cell
HSSFCell cell = row.createCell(j - 1);
// 获取数据对象
Object obj = objects[j - 1];
// 填写数据
cell.setCellValue(new HSSFRichTextString(
getStringValueFromObject(obj)));
}
}
// rowcount 归零判断 归零 新Sheet页面的开始
if (rowCount == MAX_ROW_SIZE) {
rowCount = 0;
}
Contants.expCount++;
}
}
}
部分代码解释:由于早期的excel最大的行数只能到65535,所以在这地方做了判断,但数据条数大于这个数的时候,重新开一个sheet页,进行写入。
将book写到excel中:
- public String downLoad(HSSFWorkbook workbook, String fileName,
- String savePath) throws IOException {
- FileOutputStream fos = null;
- ByteArrayOutputStream byteos = null;
- try {
- // 将HSSFWorkbook 写入字节流
- byteos = new ByteArrayOutputStream();
- workbook.write(byteos);
- // 获取所有字节及其长度
- byte[] xlsBytes = byteos.toByteArray();
- int length = xlsBytes.length;
- // 获取 流ServletOutputStream
- File file = new File(savePath + File.separator + fileName);
- if (!file.getParentFile().exists())
- file.getParentFile().mkdirs();
- if (!file.exists())
- file.createNewFile();
- fos = new FileOutputStream(file);
- fos.write(xlsBytes);
- System.out.println("End of transform the file :" + fileName);
- fos.flush();
- } catch (final IOException e) {
- throw e;
- } finally {
- // 关闭字节流
- if (byteos != null) {
- byteos.close();
- }
- if (fos != null)
- try {
- fos.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- return null;
- }
public String downLoad(HSSFWorkbook workbook, String fileName,
String savePath) throws IOException {
FileOutputStream fos = null;
ByteArrayOutputStream byteos = null;
try {
// 将HSSFWorkbook 写入字节流
byteos = new ByteArrayOutputStream();
workbook.write(byteos);
// 获取所有字节及其长度
byte[] xlsBytes = byteos.toByteArray();
int length = xlsBytes.length;
// 获取 流ServletOutputStream
File file = new File(savePath + File.separator + fileName);
if (!file.getParentFile().exists())
file.getParentFile().mkdirs();
if (!file.exists())
file.createNewFile();
fos = new FileOutputStream(file);
fos.write(xlsBytes);
System.out.println("End of transform the file :" + fileName);
fos.flush();
} catch (final IOException e) {
throw e;
} finally {
// 关闭字节流
if (byteos != null) {
byteos.close();
}
if (fos != null)
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
这块就是将book写到对于的excel文件中,这里的filename可以自己给定。