熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

Java Poi 操作Excle

2013-11-23 19:30:03  來源: Java核心技術 

  創建一個新的Excle工作薄view plain // 創建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook()

  // 在Excel工作簿中建一工作表其名為缺省值 也可以指定Sheet名稱HSSFSheet sheet = workbookcreateSheet()//HSSFSheet sheet = workbookcreateSheet(SheetName

   創建新行(row)並將單元格(cell)放入其中 行號從開始計算

  view plain HSSFRow row = sheetcreateRow((short) 設置sheet名稱和單元格內容為中文view plain wbsetSheetName(n 中文HSSFCellENCODING_UTF_cellsetEncoding((short) cellsetCellValue(中文設置列寬行高view plain sheetsetColumnWidth((short)column(short)width)rowsetHeight((short)height)添加區域合並單元格view plain Region region = new Region((short)rowFrom(short)columnFrom(short)rowTo(short)columnTo)sheetaddMergedRegion(region)//得到所有區域sheetgetNumMergedRegions()

  設置單元格邊框格式虛線HSSFCellStyleBORDER_DOTTED實線HSSFCellStyleBORDER_THIN view plain public static HSSFCellStyle getCellStyle(short type){ HSSFWorkbook wb = new HSSFWorkbook()HSSFCellStyle style = wbcreateCellStyle()stylesetBorderBottom(type)//下邊框stylesetBorderLeft(type)//左邊框stylesetBorderRight(type)//右邊框stylesetBorderTop(type)//上邊框return style} 設置字體和內容位置view plain HSSFFont f  = wbcreateFont()fsetFontHeightInPoints((short) //字號fsetBoldweight(HSSFFontBOLDWEIGHT_NORMAL)//加粗stylesetFont(f)stylesetAlignment(HSSFCellStyleALIGN_CENTER)//左右居中stylesetVerticalAlignment(HSSFCellStyleVERTICAL_CENTER)//上下居中stylesetRotation(short rotation)//單元格內容的旋轉的角度HSSFDataFormat df = wbcreateDataFormat()stylesetDataFormat(dfgetFormat(%))//設置單元格數據格式cellsetCellFormula(string)//給單元格設公式stylesetRotation(short rotation)//單元格內容的旋轉的角度cellsetCellStyle(style)插入圖片view plain //先把讀進來的圖片放到一個ByteArrayOutputStream中以便產生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream()BufferedImage bufferImg = ImageIOread(new File(okjpg))ImageIOwrite(bufferImgjpgbyteArrayOut)//讀進一個excel模版FileInputStream fos = new FileInputStream(filePathName+/stencilxltfs = new POIFSFileSystem(fos)//創建一個工作薄HSSFWorkbook wb = new HSSFWorkbook(fs)HSSFSheet sheet = wbgetSheetAt(HSSFPatriarch patriarch = sheetcreateDrawingPatriarch()HSSFClientAnchor anchor = new HSSFClientAnchor((short) (short)patriarchcreatePicture(anchor wbaddPicture(byteArrayOuttoByteArray()HSSFWorkbookPICTURE_TYPE_JPEG))

  例java view plain import javaioFileOutputStream

  import orgapachepoihssfusermodelHSSFCellimport orgapachepoihssfusermodelHSSFCellStyleimport orgapachepoihssfusermodelHSSFFontimport orgapachepoihssfusermodelHSSFRowimport orgapachepoihssfusermodelHSSFSheetimport orgapachepoihssfusermodelHSSFWorkbookimport orgapachepoihssfusermodelHSSFRichTextStringimport orgapachepoihssfusermodelHSSFDataFormatimport orgapachepoihssfusermodelHSSFCommentimport orgapachepoihssfusermodelHSSFPatriarchimport orgapachepoihssfusermodelHSSFClientAnchor

  public class PoiCreateExcelTest ……{ public static void main(String[] args) ……{ /** *//** * @see <a >>For more</a> */ // 創建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook()

  // 在Excel工作簿中建一工作表其名為缺省值 也可以指定Sheet名稱HSSFSheet sheet = workbookcreateSheet()//HSSFSheet sheet = workbookcreateSheet(SheetName

  // 用於格式化單元格的數據HSSFDataFormat format = workbookcreateDataFormat()

  // 創建新行(row)並將單元格(cell)放入其中 行號從開始計算

  HSSFRow row = sheetcreateRow((short)

  // 設置字體HSSFFont font = workbookcreateFont()fontsetFontHeightInPoints((short) //字體高度fontsetColor(HSSFFontCOLOR_RED) //字體顏色fontsetFontName(黑體 //字體fontsetBoldweight(HSSFFontBOLDWEIGHT_BOLD) //寬度fontsetItalic(true) //是否使用斜體//        fontsetStrikeout(true) //是否使用劃線

  // 設置單元格類型HSSFCellStyle cellStyle = workbookcreateCellStyle()cellStylesetFont(font)cellStylesetAlignment(HSSFCellStyleALIGN_CENTER) //水平布局居中cellStylesetWrapText(true)

  // 添加單元格注釋// 創建HSSFPatriarch對象HSSFPatriarch是所有注釋的容器

  HSSFPatriarch patr = sheetcreateDrawingPatriarch()// 定義注釋的大小和位置詳見文檔HSSFComment comment = patrcreateComment(new HSSFClientAnchor( (short) (short) ))// 設置注釋內容commentsetString(new HSSFRichTextString(可以在POI中添加注釋!))// 設置注釋作者 當鼠標移動到單元格上是可以在狀態欄中看到該內容

  commentsetAuthor(Xuys

  // 創建單元格HSSFCell cell = rowcreateCell((short) HSSFRichTextString hssfString = new HSSFRichTextString(Hello World!cellsetCellValue(hssfString)//設置單元格內容cellsetCellStyle(cellStyle)//設置單元格樣式cellsetCellType(HSSFCellCELL_TYPE_STRING)//指定單元格格式數值公式或字符串cellsetCellComment(comment)//添加注釋

  //格式化數據row = sheetcreateRow((short) cell = rowcreateCell((short) cellsetCellValue(cellStyle = workbookcreateCellStyle()cellStylesetDataFormat(formatgetFormat())cellsetCellStyle(cellStyle)

  row = sheetcreateRow((short) cell = rowcreateCell((short) cellsetCellValue(cellStyle = workbookcreateCellStyle()cellStylesetDataFormat(formatgetFormat(###))cellsetCellStyle(cellStyle)

  sheetautoSizeColumn((short) //調整第一列寬度sheetautoSizeColumn((short) //調整第二列寬度sheetautoSizeColumn((short) //調整第三列寬度sheetautoSizeColumn((short) //調整第四列寬度

  try ……{ FileOutputStream fileOut = new FileOutputStream(C\xlsworkbookwrite(fileOut)fileOutclose()} catch (Exception e) ……{ Systemoutprintln(etoString())}

  }如果編譯器提示沒有autoSizeColumn這個方法那可能是你的poi版本太低的緣故我用的是poi版本

  例Grails view plain /** * Excel導出方法導出班級工作情況統計列表* @param * titleList 標題集合* classTableInstanceListquestionList 數據集合* * out OutputStream out = responsegetOutputStream()* @return * */ def exportWorkList(titleListclassListquestionListoutttList){

  Workbook wb = new HSSFWorkbook()Sheet sheet = wbcreateSheet(sheet //班級工作情況統計

  HSSFRow   row   =   sheetcreateRow((short)HSSFRow   row   =   sheetcreateRow((short)HSSFCell   cell   =   rowcreateCell((short)HSSFCell   cell   =   rowcreateCell((short)

  // 設置字體HSSFFont font = wbcreateFont()//fontsetFontHeightInPoints((short) //字體高度//fontsetColor(HSSFFontCOLOR_RED) //字體顏色//fontsetFontName(黑體 //字體fontsetBoldweight(HSSFFontBOLDWEIGHT_BOLD) //寬度

  // 設置單元格類型HSSFCellStyle cellStyle = wbcreateCellStyle()cellStylesetFont(font)cellStylesetAlignment(HSSFCellStyleALIGN_CENTER) //水平布局居中cellStylesetWrapText(true)

  HSSFCellStyle cellStyle = wbcreateCellStyle()cellStylesetAlignment(HSSFCellStyleVERTICAL_TOP) //垂直布局居上cellStylesetWrapText(true)       //設置自動換行

  //設置單元格寬度sheetsetColumnWidth(       //序列sheetsetColumnWidth(       //區縣名稱sheetsetColumnWidth(       //基地名稱sheetsetColumnWidth(       //班級名稱sheetsetColumnWidth(       //班級狀態

  sheetsetColumnWidth(      //拓寬問題的單元格寬度

  sheetsetColumnWidth(      //評價最高的老師sheetsetColumnWidth(      //評價最低的老師

  titleListeachWithIndex {pi> if(i>= && i<=){ cell = rowcreateCell(+*(i))sheetaddMergedRegion(new Region((short)(short)))//合並區域第一行sheetaddMergedRegion(new Region((short)(+*(i))(short)(+*(i)+)))//指定合並區域cellsetCellValue(p)cellsetCellStyle(cellStyle)//設置單元格樣式

  }           } }

  try { wbwrite(out)outclose()} catch (FileNotFoundException e) { //eprintStackTrace()}catch(IOException e){ //eprintStackTrace()}


From:http://tw.wingwit.com/Article/program/Java/hx/201311/26981.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.