1.创建工作簿(WORKBOOK)
HSSFWorkbook wb = newHSSFWorkbook();
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
2.创建工作表(SHEET)
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet1= wb.createSheet("new sheet");
HSSFSheet sheet2= wb.createSheet("second sheet");
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
3.创建单元格(CELL)
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");//Create a row and put some cells in it. Rows are 0 based.
HSSFRow row= sheet.createRow((short)0);//Create a cell and put a value in it.
HSSFCell cell= row.createCell((short)0);
cell.setCellValue(1);//Or do it on one line.
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
row.createCell((short)3).setCellValue(true);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
4.创建指定单元格式的单元格
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");//Create a row and put some cells in it. Rows are 0 based.
HSSFRow row= sheet.createRow((short)0);//Create a cell and put a date value in it. The first cell is not styled//as a date.
HSSFCell cell= row.createCell((short)0);
cell.setCellValue(newDate());//we style the second cell as a date (and time). It is important to//create a new cell style from the workbook otherwise you can end up//modifying the built in style and effecting not only this cell but other cells.
HSSFCellStyle cellStyle=wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell= row.createCell((short)1);
cell.setCellValue(newDate());
cell.setCellStyle(cellStyle);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
5.单元格的不同格式
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");
HSSFRow row= sheet.createRow((short)2);
row.createCell((short) 0).setCellValue(1.1);
row.createCell((short) 1).setCellValue(newDate());
row.createCell((short) 2).setCellValue("a string");
row.createCell((short) 3).setCellValue(true);
row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
6.单元格的不通对齐方式
public static voidmain(String[] args)throwsIOException
{
HSSFWorkbook wb= newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");
HSSFRow row= sheet.createRow((short) 2);
createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}/*** Creates a cell and aligns it a certain way.
*
*@paramwb the workbook
*@paramrow the row to create the cell in
*@paramcolumn the column number to create the cell in
*@paramalign the alignment for the cell.*/
private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, shortalign)
{
HSSFCell cell=row.createCell(column);
cell.setCellValue("Align It");
HSSFCellStyle cellStyle=wb.createCellStyle();
cellStyle.setAlignment(align);
cell.setCellStyle(cellStyle);
}
7.单元格的边框设置
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");//Create a row and put some cells in it. Rows are 0 based.
HSSFRow row= sheet.createRow((short) 1);//Create a cell and put a value in it.
HSSFCell cell= row.createCell((short) 1);
cell.setCellValue(4);//Style the cell with borders all around.
HSSFCellStyle style=wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.GREEN.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLUE.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(HSSFColor.BLACK.index);
cell.setCellStyle(style);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
8.填充和颜色设置
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");//Create a row and put some cells in it. Rows are 0 based.
HSSFRow row= sheet.createRow((short) 1);//Aqua background
HSSFCellStyle style=wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell 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(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell= row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
9.合并单元格操作
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");
HSSFRow row= sheet.createRow((short) 1);
HSSFCell cell= row.createCell((short) 1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
10.字体设置
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");//Create a row and put some cells in it. Rows are 0 based.
HSSFRow row= sheet.createRow((short) 1);//Create a new font and alter it.
HSSFFont font=wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);//Fonts are set into a style so create a new one to use.
HSSFCellStyle style=wb.createCellStyle();
style.setFont(font);//Create a cell and put a value in it.
HSSFCell cell= row.createCell((short) 1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
11.自定义颜色
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet=wb.createSheet();
HSSFRow row= sheet.createRow((short) 0);
HSSFCell cell= row.createCell((short) 0);
cell.setCellValue("Default Palette");//apply some colors from the standard palette,//as in the previous examples.//we'll use red text on a lime background
HSSFCellStyle style=wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font=wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);
cell.setCellStyle(style);//save with the default palette
FileOutputStream out= new FileOutputStream("default_palette.xls");
wb.write(out);
out.close();//now, let's replace RED and LIME in the palette//with a more attractive combination//(lovingly borrowed from freebsd.org)
cell.setCellValue("Modified Palette");//creating a custom palette for the workbook
HSSFPalette palette=wb.getCustomPalette();//replacing the standard red with freebsd.org red
palette.setColorAtIndex(HSSFColor.RED.index,
(byte) 153, //RGB red (0-255)
(byte) 0, //RGB green
(byte) 0 //RGB blue
);//replacing lime with freebsd.org gold
palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);//save with the modified palette//note that wherever we have previously used RED or LIME, the//new colors magically appear
out= new FileOutputStream("modified_palette.xls");
wb.write(out);
out.close();
12.读和重写EXCEL文件
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("workbook.xls"));
HSSFWorkbook wb= newHSSFWorkbook(fs);
HSSFSheet sheet= wb.getSheetAt(0);
HSSFRow row= sheet.getRow(2);
HSSFCell cell= row.getCell((short)3);if (cell == null)
cell= row.createCell((short)3);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("a test");//Write the output to a file
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
13.在EXCEL单元格中使用自动换行
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet s=wb.createSheet();
HSSFRow r= null;
HSSFCell c= null;
HSSFCellStyle cs=wb.createCellStyle();
HSSFFont f=wb.createFont();
HSSFFont f2=wb.createFont();
cs=wb.createCellStyle();
cs.setFont( f2 );//Word Wrap MUST be turned on
cs.setWrapText(true);
r= s.createRow( (short) 2);
r.setHeight( (short) 0x349);
c= r.createCell( (short) 2);
c.setCellType( HSSFCell.CELL_TYPE_STRING );
c.setCellValue("Use /n with word wrap on to create a new line");
c.setCellStyle( cs );
s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20) ) );
FileOutputStream fileOut= new FileOutputStream( "workbook.xls");
wb.write( fileOut );
fileOut.close();
14.数字格式自定义
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("format sheet");
HSSFCellStyle style;
HSSFDataFormat format=wb.createDataFormat();
HSSFRow row;
HSSFCell cell;short rowNum = 0;short colNum = 0;
row= sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(11111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);
row= sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(11111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
15.调整工作单位置
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("format sheet");
HSSFPrintSetup ps=sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);//Create various cells and rows for spreadsheet.
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
16.设置打印区域
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");//sets the print area for the first sheet//Alternatively://wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)//Create various cells and rows for spreadsheet.
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
17.标注脚注
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("format sheet");
HSSFFooter footer=sheet.getFooter()
footer.setRight("Page " + HSSFFooter.page() + " of " +HSSFFooter.numPages() );//Create various cells and rows for spreadsheet.
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
18.使用方便的内部提供的函数
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet1= wb.createSheet( "new sheet");//Create a merged region
HSSFRow row= sheet1.createRow( (short) 1);
HSSFRow row2= sheet1.createRow( (short) 2);
HSSFCell cell= row.createCell( (short) 1);
cell.setCellValue("This is a test of merging");
Region region= new Region( 1, (short) 1, 4, (short) 4);
sheet1.addMergedRegion( region );//Set the border and border colors.
final short borderMediumDashed =HSSFCellStyle.BORDER_MEDIUM_DASHED;
HSSFRegionUtil.setBorderBottom( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderTop( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderLeft( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderRight( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);//Shows some usages of HSSFCellUtil
HSSFCellStyle style=wb.createCellStyle();
style.setIndention((short)4);
HSSFCellUtil.createCell(row,8, "This is the value of the cell", style);
HSSFCell cell2= HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");
HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);//Write out the workbook
FileOutputStream fileOut= new FileOutputStream( "workbook.xls");
wb.write( fileOut );
fileOut.close();
19.在工作单中移动行，调整行的上下位置
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("row sheet");//Create various cells and rows for spreadsheet.//Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
sheet.shiftRows(5, 10, -5);
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
20.选种指定的工作单
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("row sheet");
sheet.setSelected(true);//Create various cells and rows for spreadsheet.
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
21.工作单的放大缩小
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet1= wb.createSheet("new sheet");
sheet1.setZoom(3,4); //75 percent magnification
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
22.头注和脚注
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet= wb.createSheet("new sheet");
HSSFHeader header=sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
FileOutputStream fileOut= new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
三、重点介绍
一个excel文档必备的内容都有什么呢：
1、文档的名字和保存的位置。(文档以xls结尾)
2、sheet页，一个excel文档可以包括多个sheet页
3、行
4、单元格(有些单元格是合并的)
5、单元格样式(包括背景颜色、对其方式等)
HSSFWorkbook wb = new HSSFWorkbook(); //创建excel
HSSFSheet sheet1= wb.createSheet("new sheet"); //创建多个sheet页
HSSFSheet sheet2 = wb.createSheet("second sheet");
HSSFCellStyle style= wb.createCellStyle(); //设置单元格背景颜色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.YELLOW.index);
HSSFRow row= sheet1.createRow((short)0);//创建一行数据，
row.createCell((short)0).setCellValue(1); //给第一行数据赋值
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
HSSFCell cell= row.createCell((short)3);
cell.setCellValue(true);
cell.setCellStyle(style);//设置单元格背景颜色
sheet1.addMergedRegion(new Region(0,(short)1,1,(short)1)); //合并单元格
HSSFCellStyle style_green= wb.createCellStyle(); //设置单元格背景颜色
style_green.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style_green.setFillForegroundColor(HSSFColor.LIME.index);
style_green.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
style_green.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style_green.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style_green.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style_green.setWrapText( true );//自动回车
style_green.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置竖直方向居中
style_green.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平方向居中
sheet.setColumnWidth( (short) k, (short) ( ( 50 * 4 ) / ( (double) 1 / 20 ) ) );//设置单元格宽度
row1.setHeight((short) 0x220 );//设置行高度
页面弹出保存位置：
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
resp.setContentType("application/x-download");
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String filedisplay= sdf.format(new Date())+".xls";
filedisplay= URLEncoder.encode(filedisplay, "UTF-8");
resp.addHeader("Content-Disposition", "attachment;filename="+filedisplay);
OutputStream out=resp.getOutputStream();
wb.write(out);
背景颜色：
HSSFColor.ROYAL_BLUE
HSSFColor.TEAL
HSSFColor.LIME
HSSFColor.PALE_BLUE
HSSFColor.AQUA
HSSFColor.GREEN
HSSFColor.TURQUOISE
HSSFColor.DARK_BLUE
HSSFColor.CORNFLOWER_BLUE
HSSFColor.OLIVE_GREEN
HSSFColor.WHITE
HSSFColor.LIGHT_TURQUOISE
HSSFColor.LEMON_CHIFFON
HSSFColor.LIGHT_GREEN
HSSFColor.BLUE
HSSFColor.DARK_RED
HSSFColor.CORAL
HSSFColor.RED
HSSFColor.LIGHT_YELLOW
HSSFColor.SKY_BLUE
HSSFColor.BROWN
HSSFColor.SEA_GREEN
HSSFColor.INDIGO
HSSFColor.MAROON
HSSFColor.GREY_80_PERCENT
HSSFColor.GREY_25_PERCENT
HSSFColor.DARK_GREEN
HSSFColor.YELLOW
HSSFColor.GOLD
HSSFColor.GREY_40_PERCENT
HSSFColor.DARK_TEAL
HSSFColor.PINK
HSSFColor.ORCHID
HSSFColor.LIGHT_BLUE
HSSFColor.LIGHT_CORNFLOWER_BLUE
HSSFColor.BLACK
HSSFColor.DARK_YELLOW
HSSFColor.VIOLET
HSSFColor.LAVENDER
HSSFColor.ROSE
HSSFColor.BLUE_GREY
HSSFColor.LIGHT_ORANGE
HSSFColor.ORANGE
HSSFColor.GREY_50_PERCENT
啦啦啦
啦啦啦
扫一扫
3040
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
