大学IT网 - 最懂大学生的IT学习网站! QQ资料交流群:367606806
当前位置:大学IT网 > Java技巧 > JavaPoi操作Excle

JavaPoi操作Excle

关键词:JavaJavaPoi操作Excle  阅读(924) 赞(19)

[摘要]本文主要是对JavaPoi操作Excle的讲解,希望对大家学习JavaPoi操作Excle有所帮助。

  1.创建一个新的Excle工作薄view plain // 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();

  // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称HSSFSheet sheet = workbook.createSheet();//HSSFSheet sheet = workbook.createSheet("SheetName");

  2. 创建新行(row),并将单元格(cell)放入其中。 行号从0开始计算。

  view plain HSSFRow row = sheet.createRow((short) 1);3.设置sheet名称和单元格内容为中文view plain wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16);cell.setEncoding((short) 1);cell.setCellValue("中文");4.设置列宽、行高view plain sheet.setColumnWidth((short)column,(short)width);row.setHeight((short)height);5.添加区域,合并单元格view plain Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);sheet.addMergedRegion(region);//得到所有区域sheet.getNumMergedRegions()

  6.设置单元格边框格式虚线HSSFCellStyle.BORDER_DOTTED实线HSSFCellStyle.BORDER_THIN view plain public static HSSFCellStyle getCellStyle(short type){ HSSFWorkbook wb = new HSSFWorkbook();HSSFCellStyle style = wb.createCellStyle();style.setBorderBottom(type);//下边框style.setBorderLeft(type);//左边框style.setBorderRight(type);//右边框style.setBorderTop(type);//上边框return style;} 7.设置字体和内容位置view plain HSSFFont f  = wb.createFont();f.setFontHeightInPoints((short) 11);//字号f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗style.setFont(f);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中style.setRotation(short rotation);//单元格内容的旋转的角度HSSFDataFormat df = wb.createDataFormat();style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式cell.setCellFormula(string);//给单元格设公式style.setRotation(short rotation);//单元格内容的旋转的角度cell.setCellStyle(style);8.插入图片view plain //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));ImageIO.write(bufferImg,"jpg",byteArrayOut);//读进一个excel模版FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");fs = new POIFSFileSystem(fos);//创建一个工作薄HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheetAt(0);HSSFPatriarch patriarch = sheet.createDrawingPatriarch();HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));

  例1:java view plain import java.io.FileOutputStream;

  import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFComment;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

  public class PoiCreateExcelTest ……{ public static void main(String[] args) ……{ /** *//** * @see <a href="For'>http://poi.apache.org/hssf/quick-guide.html#NewWorkbook">For more</a> */ // 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();

  // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称HSSFSheet sheet = workbook.createSheet();//HSSFSheet sheet = workbook.createSheet("SheetName");

  // 用于格式化单元格的数据HSSFDataFormat format = workbook.createDataFormat();

  // 创建新行(row),并将单元格(cell)放入其中。 行号从0开始计算。

  HSSFRow row = sheet.createRow((short) 1);

  // 设置字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 20); //字体高度font.setColor(HSSFFont.COLOR_RED); //字体颜色font.setFontName("黑体"); //字体font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度font.setItalic(true); //是否使用斜体//        font.setStrikeout(true); //是否使用划线

  // 设置单元格类型HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中cellStyle.setWrapText(true);

  // 添加单元格注释// 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器。

  HSSFPatriarch patr = sheet.createDrawingPatriarch();// 定义注释的大小和位置,详见文档HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short) 6, 5));// 设置注释内容comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));// 设置注释作者。 当鼠标移动到单元格上是可以在状态栏中看到该内容。

  comment.setAuthor("Xuys.");

  // 创建单元格HSSFCell cell = row.createCell((short) 1);HSSFRichTextString hssfString = new HSSFRichTextString("Hello World!");cell.setCellValue(hssfString);//设置单元格内容cell.setCellStyle(cellStyle);//设置单元格样式cell.setCellType(HSSFCell.CELL_TYPE_STRING);//指定单元格格式:数值、公式或字符串cell.setCellComment(comment);//添加注释

  //格式化数据row = sheet.createRow((short) 2);cell = row.createCell((short) 2);cell.setCellValue(11111.25);cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(format.getFormat("0.0"));cell.setCellStyle(cellStyle);

  row = sheet.createRow((short) 3);cell = row.createCell((short) 3);cell.setCellValue(9736279.073);cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(format.getFormat("#,##0.0000"));cell.setCellStyle(cellStyle);

  sheet.autoSizeColumn((short)0); //调整第一列宽度sheet.autoSizeColumn((short)1); //调整第二列宽度sheet.autoSizeColumn((short)2); //调整第三列宽度sheet.autoSizeColumn((short)3); //调整第四列宽度

  try ……{ FileOutputStream fileOut = new FileOutputStream("C:\3.xls");workbook.write(fileOut);fileOut.close();} catch (Exception e) ……{ System.out.println(e.toString());}

  }如果编译器提示没有autoSizeColumn这个方法,那可能是你的poi版本太低的缘故,我用的是poi3.0版本。

  例2:Grails view plain /** * Excel导出方法,导出班级工作情况统计列表* @param * titleList 标题集合* classTableInstanceList,questionList 数据集合* * out OutputStream out = response.getOutputStream();* @return * */ def exportWorkList(titleList,classList,questionList,out,ttList){

  Workbook wb = new HSSFWorkbook();Sheet sheet = wb.createSheet("sheet1"); //班级工作情况统计

  HSSFRow   row   =   sheet.createRow((short)1);HSSFRow   row2   =   sheet.createRow((short)2);HSSFCell   cell   =   row.createCell((short)1);HSSFCell   cell2   =   row.createCell((short)2);

  // 设置字体HSSFFont font = wb.createFont();//font.setFontHeightInPoints((short) 11); //字体高度//font.setColor(HSSFFont.COLOR_RED); //字体颜色//font.setFontName("黑体"); //字体font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度

  // 设置单元格类型HSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局:居中cellStyle.setWrapText(true);

  HSSFCellStyle cellStyle1 = wb.createCellStyle();cellStyle1.setAlignment(HSSFCellStyle.VERTICAL_TOP); //垂直布局:居上cellStyle1.setWrapText(true);       //设置自动换行

  //设置单元格宽度sheet.setColumnWidth(0,1500);       //序列sheet.setColumnWidth(1,2500);       //区县名称sheet.setColumnWidth(2,7000);       //基地名称sheet.setColumnWidth(3,7000);       //班级名称sheet.setColumnWidth(4,2500);       //班级状态

  sheet.setColumnWidth(29,3000);      //拓宽问题5的单元格宽度

  sheet.setColumnWidth(35,5000);      //评价最高的老师sheet.setColumnWidth(36,5000);      //评价最低的老师

  titleList.eachWithIndex {p,i-> if(i>=5 && i<=10){ cell = row.createCell(5+5*(i-5));sheet.addMergedRegion(new Region(0,(short)0,0,(short)36));//合并区域第一行sheet.addMergedRegion(new Region(1,(short)(5+5*(i-5)),1,(short)(5+5*(i-5)+4)));//指定合并区域cell.setCellValue(p);cell.setCellStyle(cellStyle);//设置单元格样式

  }           } }

  try { wb.write(out);out.close();} catch (FileNotFoundException e1) { //e1.printStackTrace();}catch(IOException e){ //e.printStackTrace();}



相关评论