大数据导出Excel

这个在网上找到的一个例子，记录下来。

package com.zskx.pem.standalone.commons;


import java.io.IOException;
import java.io.Writer;
import java.util.Calendar;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;


public class SpreadsheetWriter {
	
	private final Writer _out;
    private int _rownum;

    public SpreadsheetWriter(Writer out){
        _out = out;
    }

    public void beginSheet() throws IOException {
        _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
        _out.write("<sheetData>\n");
    }

    public void endSheet() throws IOException {
        _out.write("</sheetData>");
        _out.write("</worksheet>");
    }

    /**
     * Insert a new row
     *
     * @param rownum 0-based row number
     */
        public void insertRow(int rownum) throws IOException {
        _out.write("<row r=\""+(rownum+1)+"\">\n");
        this._rownum = rownum;
    }

    /**
     * Insert row end marker
     */
        public void endRow() throws IOException {
        _out.write("</row>\n");
    }

    public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
        String ref = new CellReference(_rownum, columnIndex).formatAsString();
        _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
        if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
        _out.write(">");
        _out.write("<is><t>"+value+"</t></is>");
        _out.write("</c>");
    }

    public void createCell(int columnIndex, String value) throws IOException {
        createCell(columnIndex, value, -1);
    }

    public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
        String ref = new CellReference(_rownum, columnIndex).formatAsString();
        _out.write("<c r=\""+ref+"\" t=\"n\"");
        if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
        _out.write(">");
        _out.write("<v>"+value+"</v>");
        _out.write("</c>");
    }

    public void createCell(int columnIndex, double value) throws IOException {
        createCell(columnIndex, value, -1);
    }

    public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
        createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
    }
}
	

 

package com.zskx.pem.standalone.commons;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

public class ExcelUtil {

	private HSSFWorkbook wb = null;

	private HSSFSheet sheet = null;

	private HSSFRow row = null;

	private int sheetNum = 0;

	private int rowNum = 0;

	private FileInputStream fis = null;

	private File file = null;

	public ExcelUtil() {
	}

	public ExcelUtil(File file) {
		this.file = file;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 读取excel文件获得HSSFWorkbook对象
	 */
	public void open() throws IOException {
		fis = new FileInputStream(file);
		wb = new HSSFWorkbook(new POIFSFileSystem(fis));
		fis.close();
	}

	/**
	 * 返回sheet表数目
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 * sheetNum下的记录行数
	 */
	public int getRowCount() {
		if (wb == null)
			System.out.println("=============>WorkBook为空");
		HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 读取指定sheetNum的rowCount
	 */
	public int getRowCount(int sheetNum) {
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 得到指定行的内容
	 */
	public String[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行数的内容
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0)
			return null;
		String[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);
			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount + 1];
			for (int i = 0; i <= cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 读取指定列的内容
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的内容
	 */
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return "";
		String strExcelCell = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);
			if (row.getCell((short) cellNum) != null) {
				switch (row.getCell((short) cellNum).getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA:
					strExcelCell = "FORMULA ";
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					strExcelCell = String.valueOf(row.getCell((short) cellNum).getNumericCellValue());
					break;
				case HSSFCell.CELL_TYPE_STRING:
					strExcelCell = row.getCell((short) cellNum).getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					strExcelCell = "";
					break;
				default:
					strExcelCell = "";
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	/**
	 * 主函数用于测试
	 */
	public static void main(String args[]) {
		try {
			File file = new File("/usr/test/users.xlsx");
			ExcelUtil readExcel = new ExcelUtil(file);
			try {
				readExcel.open();
			} catch (IOException e) {
				e.printStackTrace();
			}
			readExcel.setSheetNum(0);
			int count = readExcel.getRowCount();
			for (int i = 0; i <= count; i++) {
				String[] rows = readExcel.readExcelLine(i);
				for (int j = 0; j < rows.length; j++) {
					System.out.print(rows[j] + " ");
				}
				System.out.print("\n");
			}
		} catch (Exception e) {
			System.out.println("对不起，读取出错...");
		}
	}
}

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Writer;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BigGridDemo {

	private static final String[] headers = { "姓名", "职称", "职务", "专业方向", "国家", "省市", "单位", "科室", "通信地址", "邮编", "单位电话",
			"传真", "手机", "电子邮箱", "角色", "备注" };

	private static final String[] fields = { "name", "professionalTitle", "duties", "specialty", "country", "province",
			"unit", "section", "addeess", "postCode", "unitPhone", "fax", "mobilePhone", "email", "userInfoRoles",
			"mark" };

	public static void main(String[] args) throws Exception {

		// Step 1. Create a template file. Setup sheets and workbook-level
		// objects such as
		// cell styles, number formats, etc.

		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet("Big Grid");

		// name of the zip entry holding sheet data, e.g.
		// /xl/worksheets/sheet1.xml
		String sheetRef = sheet.getPackagePart().getPartName().getName();

		// save the template
		FileOutputStream os = new FileOutputStream("template.xlsx");
		wb.write(os);
		os.close();

		// Step 2. Generate XML file.
		File tmp = File.createTempFile("sheet", ".xml");
		Writer fw = new FileWriter(tmp);
		fw.close();

		// Step 3. Substitute the template entry with the generated data
		FileOutputStream out = new FileOutputStream("big-grid.xlsx");
		substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
		out.close();
	}

	private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {

		Random rnd = new Random();
		Calendar calendar = Calendar.getInstance();

		SpreadsheetWriter sw = new SpreadsheetWriter(out);
		sw.beginSheet();

		// insert header row
		sw.insertRow(0);
		for (int i = 0; i < headers.length; i++) {
			sw.createCell(i, headers[i]);
		}
		sw.endRow();
		// write data rows
		for (int i = 1; i < 100000; i++) {
			sw.insertRow(i);
			sw.createCell(0, "name" + i);
			sw.createCell(1, "主任医师");
			sw.createCell(2, "职务" + i);
			if (i % 2 == 0)
				sw.createCell(3, "大厨");
			sw.createCell(3, "大厨@二厨");
			sw.createCell(4, "境内");
			sw.createCell(5, "西安");
			sw.createCell(6, "中盛剀欣");
			sw.createCell(7, "开发");
			sw.createCell(8, "科技二路");
			sw.createCell(9, "110");
			sw.createCell(10, "710000");
			sw.createCell(11, "120");
			sw.createCell(12, "130");
			sw.createCell(13, "140");
			sw.createCell(14, "[e-mail]");
			sw.createCell(15, "编辑");

			sw.endRow();

			calendar.roll(Calendar.DAY_OF_YEAR, 1);
		}
		sw.endSheet();
	}

	private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {

		ZipFile zip = new ZipFile(zipfile);

		ZipOutputStream zos = new ZipOutputStream(out);

		@SuppressWarnings("unchecked")
		Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
		while (en.hasMoreElements()) {
			ZipEntry ze = en.nextElement();
			if (!ze.getName().equals(entry)) {
				zos.putNextEntry(new ZipEntry(ze.getName()));
				InputStream is = zip.getInputStream(ze);
				copyStream(is, zos);
				is.close();
			}
		}
		zos.putNextEntry(new ZipEntry(entry));
		InputStream is = new FileInputStream(tmpfile);
		copyStream(is, zos);
		is.close();

		zos.close();
	}

	private static void copyStream(InputStream in, OutputStream out) throws IOException {

		byte[] chunk = new byte[1024];
		int count;
		while ((count = in.read(chunk)) >= 0) {
			out.write(chunk, 0, count);
		}
	}

}