/*** */package cn.sh.ideal.common;import java.io.BufferedInputStream;import java.io.ByteArrayInputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Random;import org.apache.poi.POIDocument;import org.apache.poi.hdgf.HDGFDiagram;import org.apache.poi.hpbf.HPBFDocument;import org.apache.poi.hslf.HSLFSlideShow;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.util.HSSFColor;import org.apache.poi.hwpf.HWPFDocument;import org.apache.poi.hwpf.extractor.WordExtractor;import org.apache.poi.hwpf.usermodel.Paragraph;import org.apache.poi.hwpf.usermodel.Range;import org.apache.poi.poifs.filesystem.DirectoryEntry;import org.apache.poi.poifs.filesystem.DocumentEntry;import org.apache.poi.poifs.filesystem.POIFSFileSystem;/*** @author liheng**/public class Poi extends AbstractSupport { // This holds the common functionality for all POI Document classes. // Currently, this relates to Document Information Properties private POIDocument doc; // Excel private HSSFWorkbook workbook; // Excel Sheet private HSSFSheet sheet; // Excel row private HSSFRow excelrow; // Word private HWPFDocument worddoc; // Word private WordExtractor wordext; // Powerpoint private HSLFSlideShow silideshow; // Publisher private HPBFDocument publidoc; // Visio and ... private HDGFDiagram diagram; /** Excel function begin **/ public HSSFWorkbook readExcel(String filename) throws FileNotFoundException,IOException { workbook = new HSSFWorkbook(new FileInputStream(filename)); return workbook; } public HSSFSheet readSheet(int index) { if(Assert.isNotNull(workbook)) { sheet = workbook.getSheetAt(index); } return sheet; } public HSSFSheet readSheet(String name) { if(Assert.isNotNull(workbook)) { sheet = workbook.getSheet(name); } return sheet; } public HSSFRow readRow(int index) { if(Assert.isNotNull(sheet)) { excelrow = sheet.getRow(index); } return excelrow; } public HSSFCell readCell(int row,int column) { HSSFCell cell = null; if(Assert.isNotNull(sheet)) { cell = sheet.getRow(row).getCell(column); } return cell; } public List getListMap(String filename,int stratrow)throws FileNotFoundException,IOException { List list = new ArrayList(); Map map = new HashMap(); workbook = null; sheet = null; workbook = new HSSFWorkbook(new FileInputStream(filename)); sheet = workbook.getSheetAt(0); for(int i=stratrow;i<=sheet.getLastRowNum();i++) { map.clear(); // Old pakage sheet.getRow(i).getCell((short)j) for(int j=0;j<sheet.getRow(i).getLastCellNum();j++) { if(null == sheet.getRow(i).getCell(j)) { map.put("A"+j, " "); continue; } // CELL_TYPE_NUMERIC 数值型 if(HSSFCell.CELL_TYPE_NUMERIC == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, (long)sheet.getRow(i).getCell(j).getNumericCellValue()); } // CELL_TYPE_STRING 字符串型 if(HSSFCell.CELL_TYPE_STRING == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, sheet.getRow(i).getCell(j).toString()); } // CELL_TYPE_FORMULA 公式型 if(HSSFCell.CELL_TYPE_FORMULA == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, sheet.getRow(i).getCell(j).getCellFormula().toString()); } // CELL_TYPE_BLANK 空值 if(HSSFCell.CELL_TYPE_BLANK == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, " "); } // CELL_TYPE_BOOLEAN 布尔型 if(HSSFCell.CELL_TYPE_BOOLEAN == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, sheet.getRow(i).getCell(j).getBooleanCellValue()); } // CELL_TYPE_ERROR 错误 if(HSSFCell.CELL_TYPE_ERROR == sheet.getRow(i).getCell(j).getCellType()) { map.put("A"+j, " "); } } System.out.println(map); } return list; } public void writeExce(String filename,String sheetname,String header[],String key[],List<Map<Object,Object>> list) throws FileNotFoundException,IOException { OutputStream out = new FileOutputStream(new File(filename)); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = null; // 创建Sheet时设置Sheet名称 if(Assert.isNotEmpty(sheetname)) { sheet = workbook.createSheet(sheetname); } else { sheet = workbook.createSheet(); } writeExceHeader(workbook,sheet,header); writeExceData(sheet,header,key,list); workbook.write(out); out.close(); } private void writeExceHeader(HSSFWorkbook workbook,HSSFSheet sheet,String header[]) { if(Assert.isNotNull(sheet) && Assert.isNotEmpty(header)) { HSSFRow row = sheet.createRow(0); HSSFCell cell = null; // 创建字体 HSSFFont font = workbook.createFont(); // 字体颜色 font.setColor(HSSFFont.COLOR_NORMAL); // 设置字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体大小 font.setFontHeight((short)220); // 创建Cell格式 HSSFCellStyle cellStyle= workbook.createCellStyle(); // 设置背景样式 // cellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS); // 设置背景颜色 //cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index); //cellStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS); // 设置背景颜色 cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置CELL字体 cellStyle.setFont(font); // 设置居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //cellStyle.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT); // 设置格式 //cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); for(int i=0;i<header.length;i++) { cell = row.createCell(i); cell.setCellStyle(cellStyle); //cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(header[i]); } } } private void writeExceData(HSSFSheet sheet,String header[],String key[],List<Map<Object,Object>> list) { HSSFRow row = null; HSSFCell cell = null; if(Assert.isNotNull(sheet)&& Assert.isNotEmpty(key) && Assert.isNotEmpty(list)) { int rownum = 0; if(Assert.isNotEmpty(header)) rownum = 1; for(Map<Object,Object> map : list) { row = sheet.createRow(rownum++); for(int i=0;i<key.length;i++) { cell = row.createCell(i); cell.setCellValue(Assert.toString(map.get(key[i]))); } } } } /** Excel function end **/ /** Word function begin **/ public HWPFDocument readWord(String filename) throws IOException { worddoc = new HWPFDocument(new FileInputStream(filename)); return worddoc; } public WordExtractor readWord(String filename,boolean t) throws IOException { wordext = new WordExtractor(new FileInputStream(filename)); return wordext; } /** * 读取Word文本内容 * <pre class="code">Poi.readWordText(filename);</pre> * @param 文件路径文件名 * @return 返回<code>String</code>文本内容 */ public String readWordText(String filename)throws IOException { wordext = new WordExtractor(new FileInputStream(filename)); //return wordext.getTextFromPieces(); return wordext.getText(); } public StringBuffer readWordText() { StringBuffer strbuff = new StringBuffer(); if(Assert.isNotNull(worddoc)) { // 取得word文档的范围 Range r = worddoc.getRange (); //StyleSheet styleSheet = worddoc.getStyleSheet (); // 取得段落数 int lenParagraph = r.numParagraphs (); for (int i = 0; i < lenParagraph; i++) { Paragraph p = r.getParagraph (i); //StyleDescription paragraphStyle = styleSheet.getStyleDescription (p.getStyleIndex ()); //String styleName = paragraphStyle.getName(); strbuff.append(p.text ()); } } return strbuff; } public void writeWord(String filename,String wordtext)throws FileNotFoundException,UnsupportedEncodingException,IOException { // 创建写文件流 OutputStream out = new FileOutputStream(new File(filename)); //OutputStreamWriter outwrite = new OutputStreamWriter (new FileOutputStream(new File(filename)), ENCODING ); //以下两句代码不能省略，否则输出的是乱码 POIFSFileSystem fs = new POIFSFileSystem(); DirectoryEntry directory = fs.getRoot(); // 根据系统默认编码格式 获取字节数组数据 byte[] a = new String( wordtext ).getBytes( ENCODING ); InputStream input = new ByteArrayInputStream(a); DocumentEntry de = directory.createDocument("WordDocument", input); // 将poi文件流向文件输出流 fs.writeFilesystem(out); input.close(); out.flush(); out.close(); //HWPFDocument worddoc = new HWPFDocument(); } /** Word function end **/ public static void main(String arg[]) {// Poi poi = new Poi();// // String head[] = new String[]{"序列号","姓名","性别","地址","电话"};// String key[] = new String[]{"id","name","sex","add","phone"};// List<Map<Object,Object>> list = new ArrayList<Map<Object,Object>>();// Random random = new Random();// // for(int i=0;i<20;i++)// {// Map<Object,Object> map = new HashMap<Object,Object>();// map.put("id", random.nextInt(10));// map.put("name", random.nextInt(10));// map.put("sex", random.nextInt(10));// map.put("add", random.nextInt(10));// map.put("phone", random.nextInt(10));// list.add(map);// }// // try// {// poi.writeExce("c://test.xls","", head, key, list);// }// catch(Exception ex)// {// // }// try// {// poi.readWord("D://理想//业务支撑平台//业务支撑平台企业户名合法性判断_概要设计说明书.doc");// System.out.println(poi.readWordText());// poi.writeWord("c://test.doc");// System.out.println(poi.readWordText("D://理想//业务支撑平台//业务支撑平台企业户名合法性判断_概要设计说明书.doc"));// }// catch(Exception ex)// {// System.out.println(ex.getMessage());// } try { Poi obj = new Poi(); //String file = "D://合肥//电费表.xls"; String file = "D://合肥//合肥、巢湖水电气数据//巢湖//2009年1月份居民客户电量电费.xls"; //String file = "D://合肥//水，电，煤数据导入格式//0905电费.xls"; obj.readExcel(file); obj.readSheet(0); obj.getListMap(file,1); } catch(Exception ex) { System.out.println(ex.getMessage()); } }}
