/**
*
*/
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());
}
}
}