java 用jxl导出XLS文件，大量数据多个sheet存储

最新推荐文章于 2024-06-27 08:00:00 发布

baowg1212

最新推荐文章于 2024-06-27 08:00:00 发布

阅读量6.3k

点赞数

文章标签： java exception string microsoft office cmd

本文链接：https://blog.csdn.net/baowg1212/article/details/7048691

版权

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExportXls {

	/**
	 * @param args
	 */
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		// 一行表头
		List<String> headList = new ArrayList<String>();
		
		for (int i = 0; i < 6; i++) {
			
			headList.add("Head" + i);
		}
		
		// 一行数据
		List<String> row = new ArrayList<String>();
		
		for (int i = 0; i < 6; i++) {
			
			row.add("body" + i);
		}
		
		// 全部数据
		List<List<String>> data = new ArrayList<List<String>>();
		
		for (int i = 0; i < 100000; i++) {
			
			data.add(row);
		}
		
		writeIntoXLS(headList, data, "C:/Result.xls");
		
		
	}

	private static int sheetSize = 10000;

	
	public static void writeIntoXLS(List<String> headList, List<List<String>> bodyList, String xlsFull) {
		
		WritableWorkbook wwb= null;
		
		try{
			
			int sheetNum = 1;//当前sheet索引
	   
			int recNum = 0; // 当前记录索引
			
			//生成xls文件
			File writeFile = new File(xlsFull);
   
			//检查文件是否已存在，存在则删除
			if(writeFile.exists()) writeFile.delete();
			
			//建立新的文件
			writeFile.createNewFile();
			
			//整理数据流，WritableWorkbook
			wwb = Workbook.createWorkbook(writeFile);
			
			//开始分SHEET，写数据
			while(true){
				
				int rowNum = 0;
				
				String sheetName = "sheet" + sheetNum;
				
				//建立SHEET
				WritableSheet ws = wwb.createSheet(sheetName, sheetNum);
				
				sheetNum++;
				
				// 设置样式
				WritableCellFormat format = new WritableCellFormat(
						NumberFormats.TEXT);
				
				//写入表头信息
				for(int i = 0; i < headList.size(); i++) {
					
					ws.addCell(new Label(i, rowNum, headList.get(i), format));
					
				}
				
				rowNum++;
				
				//写入表数据行信息
				if(!bodyList.isEmpty()) {
					
					int sheetRecNum = 0;
					
					for(int i = recNum; i < bodyList.size(); i++, recNum++, rowNum++, sheetRecNum++) {
						
						//单个SHEET数据行超过SHEET应达到数据限额，则停止写入
						if(sheetRecNum >= sheetSize) break;
						
						List<String> row = bodyList.get(i);
						
						for(int j=0; j<row.size(); j++) {
							
							ws.addCell(new Label(j, rowNum, row.get(j).toString(), format));
						}
					}
				}
				
				//wwb.write();--------------不能放到这里，移到下面（这个是从网上看到别人的经验得来）
				//数据写完，退出
				if(recNum >= bodyList.size()) break;
			}
			
			//PUSH当前内容到XLS文件中--------这一步很关键，sheet写好之后就要push到xls文件中，然后再新建
			wwb.write();
			
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			//关闭所有数据流
			try { wwb.close(); } catch(Exception e) { }
			Runtime r = Runtime.getRuntime();   
			Process p = null ;   
			String cmd[] = {"C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" , "c:\\Result.xls" };   
			try {   
				p = r.exec(cmd);   
			}   
			catch (Exception e){   
				System.out.println("error executing: " +cmd[ 0 ]);   
			}
		}
	}
}