package poi;

import java.io.FileOutputStream;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class Test2 {

	public static void main(String[] args) throws Exception {
		//run two times to avoid class loading impact etc.
		doWb(false, "c:/0/01.xlsx");
		doWb(true, "c:/0/02.xlsx");
		doWb(false, "c:/0/03.xlsx");
		doWb(true, "c:/0/04.xlsx");
	}

	private static void doWb(boolean doBorders, String fileName) throws Exception {
		System.out.println("fileName = " + fileName + ", doBorders = " + doBorders);
		long time = System.currentTimeMillis();
		Workbook wb = new XSSFWorkbook();

		//prepare three different styles
		Font f1 = wb.createFont();
		f1.setFontHeightInPoints((short)10);
		f1.setFontName("Times New Roman");
		f1.setColor(IndexedColors.RED.getIndex());
		Font f2 = wb.createFont();
		f2.setFontHeightInPoints((short)8);
		f2.setFontName("Times New Roman");
		f2.setColor(IndexedColors.GREEN.getIndex());
		Font f3 = wb.createFont();
		f3.setFontHeightInPoints((short)10);
		f3.setBoldweight(Font.BOLDWEIGHT_BOLD);
		f3.setFontName("Arial CE");
		f3.setColor(IndexedColors.BLUE.getIndex());
		CellStyle s1 = wb.createCellStyle();
		s1.setFont(f1);
		CellStyle s2 = wb.createCellStyle();
		s2.setFont(f2);
		CellStyle s3 = wb.createCellStyle();
		s3.setFont(f3);
		List<CellStyle> styles = Arrays.asList(s1, s2, s3);

		Sheet sh = wb.createSheet("Sheet1");
		//three cell areas 10x10 using the styles prepared above
		for (int nn = 0; nn < 3; nn++) {
			for (int ii = 0; ii < 10; ii++) {
				Row rr = sh.createRow(nn*10 + ii);
				for (int jj = 0; jj < 10; jj++) {
					Cell cc = rr.getCell(jj, Row.CREATE_NULL_AS_BLANK);
					cc.setCellStyle(styles.get(nn));
					cc.setCellValue(nn * 10000 + ii * 100 + jj);
				}
			}
		}

		long time2 = System.currentTimeMillis();
		if (doBorders) {
			thickBorder(wb, sh, new CellRangeAddress(0, 29, 0, 9));
			for (int nn = 0; nn <=20 ; nn += 10) {
				thinBorder(wb, sh, new CellRangeAddress(nn + 1, nn + 2, 1, 2));
				thinBorder(wb, sh, new CellRangeAddress(nn + 1, nn + 2, 7, 8));
				mediumBorder(wb, sh, new CellRangeAddress(nn + 7, nn + 8, 1, 2));
				mediumBorder(wb, sh, new CellRangeAddress(nn + 7, nn + 8, 7, 8));
				thickBorder(wb, sh, new CellRangeAddress(nn + 4, nn + 5, 5, 6));
				thickBorder(wb, sh, new CellRangeAddress(nn + 4, nn + 5, 5, 6));
			}

		}
		System.out.println("  elapsed bordering: " + (System.currentTimeMillis() - time2));
		System.out.println("  styles count: " + wb.getNumCellStyles());

		FileOutputStream out = new FileOutputStream(fileName);
        wb.write(out);
        out.close();
        System.out.println("  elapsed total: " + (System.currentTimeMillis() - time));
	}


	private static void thinBorder(Workbook wb, Sheet sh, CellRangeAddress rng) {
		RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, rng, sh, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, rng, sh, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_THIN, rng, sh, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_THIN, rng, sh, wb);
	}


	private static void mediumBorder(Workbook wb, Sheet sh, CellRangeAddress rng) {
		RegionUtil.setBorderBottom(CellStyle.BORDER_MEDIUM, rng, sh, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_MEDIUM, rng, sh, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_MEDIUM, rng, sh, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_MEDIUM, rng, sh, wb);
	}

	private static void thickBorder(Workbook wb, Sheet sh, CellRangeAddress rng) {
		RegionUtil.setBorderBottom(CellStyle.BORDER_THICK, rng, sh, wb);
		RegionUtil.setBorderLeft(CellStyle.BORDER_THICK, rng, sh, wb);
		RegionUtil.setBorderRight(CellStyle.BORDER_THICK, rng, sh, wb);
		RegionUtil.setBorderTop(CellStyle.BORDER_THICK, rng, sh, wb);
	}

}