java

package com.zcj.poi;

import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.junit.Test;

public class PoiDemo001 {
	
	@Test
	public void test() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		String safeName = WorkbookUtil.createSafeSheetName("[zhou'sheet2*?]");//创建安全的工作簿名
		Sheet sheet2 = wb.createSheet(safeName);
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	
	@Test
	public void testCells() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		
		CreationHelper creationHelper = wb.getCreationHelper();
		
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(0);//定位行
		Cell cell = row.createCell(0);//定位单元格
		cell.setCellValue("cell");
		
		row.createCell(1).setCellValue(9.9);
		
		row.createCell(2).setCellValue(creationHelper.createRichTextString("This is a String"));
		
		row.createCell(3).setCellValue(true);
		
		String safeName = WorkbookUtil.createSafeSheetName("[zhou'sheet2*?]");//创建安全的工作簿名
		Sheet sheet2 = wb.createSheet(safeName);
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	
	@Test
	public void testCreatingDateCells() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		
		CreationHelper creationHelper = wb.getCreationHelper();
		
		CellStyle style = wb.createCellStyle();//创建样式
		style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));//设置时间样式
		
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(0);//定位行
		
		Cell cell = null;
		cell = row.createCell(0);
		cell.setCellValue(new Date());//不使用样式
		
		cell = row.createCell(1);
		cell.setCellValue(new Date());
		cell.setCellStyle(style);//使用样式
		
		cell = row.createCell(2);
		cell.setCellValue(Calendar.getInstance());//使用日历
		cell.setCellStyle(style);
		
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	
	@Test
	public void testWorkingWithDifferentTypesOfCells() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		
		CreationHelper creationHelper = wb.getCreationHelper();
		
		CellStyle style = wb.createCellStyle();//创建样式
		style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));//设置时间样式
		
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(0);//定位行
		
		row.createCell(0).setCellValue(true);
		row.createCell(1).setCellValue(Calendar.getInstance());
		row.createCell(2).setCellValue(new Date());
		row.createCell(3).setCellValue(9.9);
		row.createCell(4).setCellValue(creationHelper.createRichTextString("This is a String"));
		row.createCell(5).setCellValue("String");
		row.createCell(6).setCellType(CellType.ERROR);
		
		
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	
	@Test
	public void testDemonstratesVariousAlignmentOptions() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
				
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(0);//定位行
		row.setHeightInPoints(30);
		
		createCell(wb, row, 0, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
		createCell(wb, row, 1, HorizontalAlignment.LEFT, VerticalAlignment.CENTER);
		createCell(wb, row, 2, HorizontalAlignment.LEFT, VerticalAlignment.BOTTOM);
		createCell(wb, row, 3, HorizontalAlignment.CENTER, VerticalAlignment.TOP);
		createCell(wb, row, 4, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
		createCell(wb, row, 5, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
		createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
		createCell(wb, row, 7, HorizontalAlignment.RIGHT, VerticalAlignment.CENTER);
		createCell(wb, row, 8, HorizontalAlignment.RIGHT, VerticalAlignment.BOTTOM);
		
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	/**
	 * 设置单元格水平垂直对象方向
	 * @param wb excel
	 * @param row 行
	 * @param column 列
	 * @param halign 水平方向
	 * @param valign 垂直方向
	 */
	private static void createCell(Workbook wb,Row row,Integer column,HorizontalAlignment halign,VerticalAlignment valign){
		Cell cell = row.createCell(column);
		cell.setCellValue("Align It");
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(halign);
		cellStyle.setVerticalAlignment(valign);
		cell.setCellStyle(cellStyle);
	}
	
	@Test
	public void testWorkingWithBorders() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		
		CreationHelper creationHelper = wb.getCreationHelper();
		
		CellStyle cellStyle = wb.createCellStyle();//创建样式
		//设置边框线条样式
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.DOUBLE);
		cellStyle.setBorderRight(BorderStyle.DOUBLE);
		//设置边框线条颜色
		cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
		cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
		cellStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
		
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(1);//定位行
		Cell cell = row.createCell(1);
		cell.setCellValue("边框");
		cell.setCellStyle(cellStyle);
		
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	@Test
	public void tests() throws Exception{
		Workbook wb = new HSSFWorkbook();//创建excel文件
		
		CreationHelper creationHelper = wb.getCreationHelper();
		
		CellStyle cellStyle = wb.createCellStyle();//创建样式
		//设置边框线条样式
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.DOUBLE);
		cellStyle.setBorderRight(BorderStyle.DOUBLE);
		//设置边框线条颜色
		cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
		cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
		cellStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
		
		Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
		
		Row row = sheet1.createRow(1);//定位行
		Cell cell = row.createCell(1);
		cell.setCellValue("边框");
		cell.setCellStyle(cellStyle);
		
		FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
		wb.write(execl);//把内容写入流
		execl.close();
	}
	@Test
	public void testss() throws Exception{
		Workbook wb = new HSSFWorkbook();
	    Sheet sheet = wb.createSheet("new sheet");

	    // Create a row and put some cells in it. Rows are 0 based.
	    Row row = sheet.createRow((short) 1);

	    // Aqua background
	    CellStyle style = wb.createCellStyle();
	    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
	    style.setFillPattern(CellStyle.BIG_SPOTS);
	    Cell cell = row.createCell((short) 1);
	    cell.setCellValue("X");
	    cell.setCellStyle(style);

	    // Orange "foreground", foreground being the fill foreground not the font color.
	    style = wb.createCellStyle();
	    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
	    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cell = row.createCell((short) 2);
	    cell.setCellValue("Y");
	    cell.setCellStyle(style);

	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
	    wb.write(fileOut);
	    fileOut.close();
	}
}