package com.zxz.pgdemo.controller;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;
import org.apache.commons.compress.archivers.zip.ZipFile;
import org.apache.poi.openxml4j.opc.internal.ZipHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
public class TestExport {
public static class MyTask implements Runnable{
public String name;
public MyTask(String name) {
this.name = name;
}
@Override
public void run() {
System.out.println("正在执行"+":Thread ID:"+Thread.currentThread().getId()+",Task Name="+name);
try {
Thread.sleep(100);
}catch (InterruptedException e){
e.printStackTrace();
}
}
}
public static void main(String[] args) throws InterruptedException{
MyTask task = new TestExport.MyTask("abc");
ExecutorService es = new ThreadPoolExecutor(5, 5, 0L, TimeUnit.MILLISECONDS,
new LinkedBlockingQueue<Runnable>(10),
new ThreadFactory() {
@Override
public Thread newThread(Runnable r) {
Thread t = new Thread(r);
t.setDaemon(true);
System.out.println("create "+t);
return t;
}
},
new RejectedExecutionHandler() {
@Override
public void rejectedExecution(Runnable r, ThreadPoolExecutor executor) {
System.out.println(r.toString() + " is discard");
}
});
for (int i = 0; i < 5; i++) {
es.submit(task);
Thread.sleep(2000);
}
}
void testExcutor() {
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(10);
fixedThreadPool.shutdown();
Executors.defaultThreadFactory();
ExecutorService cachedThreadPool = Executors.newCachedThreadPool(Executors.defaultThreadFactory());
ExecutorService workStealingPool = Executors.newWorkStealingPool();
}
void test() throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Big Grid");
Map<String, XSSFCellStyle> styles = createStyles(wb);
//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);
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
FileOutputStream stream = new FileOutputStream(tmp);
Writer fw = new OutputStreamWriter(stream, StandardCharsets.UTF_8);
generate(fw, styles);
//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);
}
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);
}
}
private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
try (ZipFile zip = ZipHelper.openZipFile(zipfile)) {
try (ZipArchiveOutputStream zos = new ZipArchiveOutputStream(out)) {
Enumeration<? extends ZipArchiveEntry> en = zip.getEntries();
while (en.hasMoreElements()) {
ZipArchiveEntry ze = en.nextElement();
if (!ze.getName().equals(entry)) {
zos.putArchiveEntry(new ZipArchiveEntry(ze.getName()));
try (InputStream is = zip.getInputStream(ze)) {
copyStream(is, zos);
}
zos.closeArchiveEntry();
}
}
zos.putArchiveEntry(new ZipArchiveEntry(entry));
try (InputStream is = new FileInputStream(tmpfile)) {
copyStream(is, zos);
}
zos.closeArchiveEntry();
}
}
}
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
Random rnd = new Random();
Calendar calendar = Calendar.getInstance();
BigGridDemo.SpreadsheetWriter sw = new BigGridDemo.SpreadsheetWriter(out);
sw.beginSheet();
//insert header row
sw.insertRow(0);
int styleIndex = styles.get("header").getIndex();
sw.createCell(0, "Title", styleIndex);
sw.createCell(1, "% Change", styleIndex);
sw.createCell(2, "Ratio", styleIndex);
sw.createCell(3, "Expenses", styleIndex);
sw.createCell(4, "Date", styleIndex);
sw.endRow();
//write data rows
for (int rownum = 1; rownum < 100000; rownum++) {
sw.insertRow(rownum);
sw.createCell(0, "Hello, " + rownum + "!");
sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
sw.createCell(4, calendar, styles.get("date").getIndex());
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
sw.endSheet();
}
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
Map<String, XSSFCellStyle> styles = new HashMap<>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HorizontalAlignment.RIGHT);
style1.setDataFormat(fmt.getFormat("0.0%"));
styles.put("percent", style1);
XSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setDataFormat(fmt.getFormat("0.0X"));
styles.put("coeff", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HorizontalAlignment.RIGHT);
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HorizontalAlignment.RIGHT);
style4.setDataFormat(fmt.getFormat("mmm dd"));
styles.put("date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style5.setFont(headerFont);
styles.put("header", style5);
return styles;
}
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
SpreadsheetWriter(Writer out){
_out = out;
}
void beginSheet() throws IOException {
_out.write("<?xml version=\"1.0\" encoding=\""+StandardCharsets.UTF_8+"\"?>" +
"<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("<sheetData>\n");
}
void endSheet() throws IOException {
_out.write("</sheetData>");
_out.write("</worksheet>");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
void insertRow(int rownum) throws IOException {
_out.write("<row r=\""+(rownum+1)+"\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
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);
}
}
public static class Producer implements Runnable {
private volatile boolean isRunning = true;
private BlockingQueue<Map> blockingQueue;
private static AtomicInteger count = new AtomicInteger();
public Producer(BlockingQueue<Map> queue) {
this.blockingQueue=queue;
}
@Override
public void run() {
System.out.println("start producer id="+Thread.currentThread().getId());
Map data = new HashMap();
try {
blockingQueue.offer(data, 2, TimeUnit.SECONDS);
}catch (InterruptedException e) {
e.printStackTrace();
Thread.currentThread().interrupt();
}
}
public void stop() {
this.isRunning=false;
}
}
}
ExcelStreamExport
©著作权归作者所有,转载或内容合作请联系作者
- 文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
- 文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
- 文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
