一、所需的jar包(http://download.csdn.net/detail/huangzebiao007/6375115)
二、主要的操作方法
1、读取excel文件
InputStream in = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(in);
2、写入excel表格
HSSFWorkbook wb = new HSSFWorkbook();
或:
HSSFWorkbook wb = new HSSFWorkbook(in);
//这里对workbook进行一些操作
OutputStream out = new FileOutputStream(file);
wb .write(out);
3、sheet操作
HSSFSheet sheet = wb.getSheetAt(0);//得到sheet,从0开始
HSSFSheet sheet = wb.getSheet(sheetName))//sheet的名字
HSSFSheet sheet = wb.createSheet(sheetName); //创建sheet
HSSFSheet sheet = wb.cloneSheet(0); //复制sheet
HSSFSheet sheet = wb.setSheetName(i, "SheetName new"); //修改sheet名
HSSFSheet sheet = wb.removeSheetAt(0); //删除sheet
wb.getNumberOfSheets() = 3 //Excel有3个sheet,得到的就是sheet的个数 //查看有多少个sheet
4、row操作
HSSFRow row = sheet.getRow(0);//得到row,从0开始
HSSFRow row = sheet.createRow(0);//创建row
sheet.removeRow(row);//删除row
//移动行:只移动内容,不牵扯行的删除和插入
//移动行(把第1行和第2行移到第5行之后)
sheet.shiftRows(0, 1, 5);
//移动行(把第3行和第4行往上移动1行)
sheet.shiftRows(2, 3, -1);
sheet.setDefaultRowHeight((short)100);//设置默认行高
sheet.getRow(2).setHeight((short)(100 * 20));//设置行高
sheet.getLastRowNum() = 3 //Excel中Sheet1表有4行数据,所以得到的数据是3
5、列操作
wb.getSheet("sheetname7").setDefaultColumnWidth(12);//设置默认列宽
sheet.setColumnWidth(0, 5 * 256);//第一列,//设置列宽
row.getLastCellNum() = 4//Excel中Sheet1表有4列数据,所以得到的数据是4
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(0, 1, 0, 2));//合并第一行和第二行的第一列到第三列范围的单元格
HSSFCell cell = row.createCell(0);//创建单元格
HSSFCell cell = row.getCell(0);//得到单元格
row.removeCell(cell);//删除单元格
6、单元格样式操作
public static HSSFCellStyle createCellStyle(
HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
// 设置上下左右四个边框
style.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
style.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
style.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
style.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
// 设置上下左右四个边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置单元格背景色,颜色参考文档
style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置字体格式,并将字体格式设置到HSSFCellStyle上
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
font.setItalic(true); //斜体
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体
font.setColor(HSSFColor.YELLOW.index); //文字颜色
style.setFont(font);
// 设置单元格居中,水平,竖直
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
7、单元格值操作
cell.setCellStyle(style);
cell.setCellValue("aaa");
public static void getCellValue(HSSFCell cell) {
if(cell == null){
return;
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
System.out.println("CELL_TYPE_BLANK");
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
System.out.println(cell.getRichStringCellValue().getString());
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
System.out.println(cell.getNumericCellValue());
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
System.out.println(cell.getBooleanCellValue());
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
System.out.println(cell.getNumericCellValue()+" 公式为:"+cell.getCellFormula());
}
}