通过
Jakarta的POI项目提供了一组操纵Windows文档的Java API 来完成对excel文件操作
该API有如下几个类提供了操作Excel文件的方便的途径:
该API有如下几个类提供了操作Excel文件的方便的途径:
- HSSFWorkbook ==>Excel电子表格中的 Book
- HSSFSheet ==>Excel电子表格中的 Sheet
- HSSFRow ==>Excel电子表格中的 行
- HSSFCell ==>Excel电子表格中的 单元格
- package business;
- import org.apache.poi.hssf.usermodel.*;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- /**
- * Jakarta的POI项目提供了一组操纵Windows文档的Java API,如下几个类提供了操作Excel文件的方便的途径:<br>
- * HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell,<br>
- * 分别代表了Excel电子表格中的Book、Sheet、行和单元格。 <br>
- */
- public class CreateExcel {
- public static void main(String[] args) throws Exception {
- write();
- System.out.println(read("d://test.xls"));
- }
- /**
- * 写时:解决中文乱码问题
- * cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- *
- * 通过 FileOutputStream 来完生成的xls文件的保存
- */
- private static void write() {
- FileOutputStream fos = null;
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet s = wb.createSheet();
- wb.setSheetName(0, "first sheet");
- StringBuffer str = new StringBuffer();
- str.append("#@#Acct GroupAttribute Change,,,,FIN_SRV- Banking,,,,,,,,,Bank Of China,,,Enterprise,,,China ConstructionBank,hailali,CH_S_ENT_NA_FSI_CCBhailali,Enterprise,Local GovernmentRegulations,the current groupname(China Construction Bank)is wrong for this account:BOC Hainan, pls helpto change it to correct group namd:Bank Of China,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,FIN_SRV- Banking,,,,,,,,,The People's Bank OfChina,,,Enterprise,,,GOVERNMENT OF CHINA,hailali,CH_S_ENT_NA_FSI_PBOChailali,Enterprise,Local GovernmentRegulations,the current groupname(Government of China )is wrong for this account:PBOC Yulin, pls help tochange it to correct group namd:The People's Bank Of China,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,ENG/UTL- NaturalGas/Electric/Water,,,,,,,,,South Power GridCorporation,,,Enterprise,,,Guangxi Power Company,hailali,CH_S_ENT_NA_LENT_POWERhailali,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: South Power Grid Corporation,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,ENG/UTL- NaturalGas/Electric/Water,,,,,,,,,South Power GridCorporation,,,Enterprise,,,Hainan PowerCorporation Limite,hailali,CH_S_ENT_NA_LENT_POWERhailali,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: South Power Grid Corporation,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,ENG/UTL- NaturalGas/Electric/Water,,,,,,,,,South Power GridCorporation,,,Enterprise,,,Datang Tanyan WaterPower Generator,hailali,CH_S_ENT_NA_LENT_POWERhailali,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: South Power Grid Corporation,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,FIN_SRV- Banking,,,,,,,,,The People's Bank OfChina,,,Enterprise,,,China ConstructionBank,haiyxu,CH_S_ENT_NA_FSI_CCBhaiyxu,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: The People's Bank Of China,OK to submit");
- str.append("#@#Acct Delete,No,DATANG TANYAN WATERPOWER GENERATOR,,,37156485,0013000000Cziiv,,,,,,,,,,,,,,hailali,CH_S_ENT_NA_LENT_POWERhailali,Enterprise,Merger &Acquisition,this account is mergein Datang Power plant, which is not belongs to hailali's account,OK to submit");
- str.append("#@#Acct Delete,No,Shenzhen Metro Co.,Ltd.,,,2797093,0013000000D2X9w,,,,,,,,,,,,,,yli2,CH_S_ENT_NA_LENT_MORyli2,Enterprise,Duplicate Account,account duplicate innamed account list, pls delete,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,ENG/UTL- NaturalGas/Electric/Water,3225272,,,,,,,,Guangdong YudeanGroup Corporation Limited,,,Enterprise,,,Huaneng Power CompanyLimited,yli2,CH_S_ENT_NA_LENT_POWERyli2,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: Guangdong Yudean Group Corporation Limited,OK to submit");
- str.append("#@#Acct GroupAttribute Change,,,,ENG/UTL- NaturalGas/Electric/Water,,,,,,,,,Guangdong YudeanGroup Corporation Limited,,,Enterprise,,,Huaneng Haimen PowerPlan,yli2,CH_S_ENT_NA_LENT_POWERyli2,Enterprise,Local GovernmentRegulations,the account should beunder FVEV group name: Guangdong Yudean Group Corporation Limited,OK to submit");
- str.append("#@#Acct Delete,Yes,Shenzhen MindrayCo.,Ltd,深圳迈瑞生物医疗电子股份有限公司,,39421686,0013000000It5ak,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with Mei Wenfeng's acct. Mindray Medical International Limited,which has no chinese name, so make confusion before,OK to submit");
- str.append("#@#Acct Delete,Yes,Zhongcheng BondCo.,Ltd,众成证券经纪有限公司,,89284977,0013000000It5Zb,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. wastransferd to CH_COMM_SZX_ISAM_01 already,OK to submit");
- str.append("#@#Acct Delete,Yes,Guangxi JinGui Pulp& Paper Co., Ltd,,,83297032,0013000000I0cpI,,,,,,,,,,,,,,wuwen,CH_COMM_GX_ISAM_01(CH_S_COMM_GX_TAM_TM),Commercial Mid-MarketTAM,Duplicate Account,Duplicate Account,OK to submit");
- str.append("#@#Acct Delete,Yes,guangxiwomen&children healthy hospital,,,34055092,0013000000IP1E3,,,,,,,,,,,,,,wuwen,CH_COMM_GX_ISAM_01(CH_S_COMM_GX_TAM_TM),Commercial Mid-MarketTAM,Duplicate Account,Duplicate Account,OK to submit");
- str.append("#@#Acct Regroup,,GUANGDONG MIDEAELECTRIC APPLIANCE COMPANY LTD,,,110081399,,,,,,,,MIDEA GROUP,,,,,,GUANGDONG MIDEAELECTRIC APPLIANCE COMPANY LTD,yasu,CH_S_ENT_GD_O_LENT_TMyasu,,Merger &Acquisition,Belong to new group,OK to submit");
- str.append("#@#Acct Delete,Yes,guangxiwomen&children healthy hospital,,,34055092,0013000000IP1E3,,,,,,,,,,,,,,wuwen,CH_COMM_GX_ISAM_01(CH_S_COMM_GX_TAM_TM),Commercial Mid-MarketTAM,Duplicate Account,Duplicate Accountwith ISAM.,OK to submit");
- str.append("#@#Acct Delete,Yes,Shenzhen MindrayCo.,Ltd,深圳迈瑞生物医疗电子股份有限公司,,39421686,0013000000It5ak,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with Mei Wenfeng's acct. Mindray Medical International Limited,which has no chinese name, so make confusion before,OK to submit");
- str.append("#@#Acct Delete,Yes,Zhongcheng BondCo.,Ltd,众成证券经纪有限公司,,89284977,0013000000It5Zb,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. wastransferd to CH_COMM_SZX_ISAM_01 already,OK to submit");
- str.append("#@#Acct Delete,Yes,Trust Mart Shanghai,,,2733443,0013000000GdUaN,,,,,,,,,,,,,,lilzhong,CH_S_ENT_SZ_LENT_TMlilzhong,Enterprise,Duplicate Account,notbelong to,OK to submit");
- str.append("#@#Acct Delete,Yes,guangdong zhong youpu tai mobile communication equipment,,,10282152,0013000000KFumZ,,,,,,,,,,,,,,kahe,CH_S_ENT_GZ_LENT_TMkahe,Enterprise,Duplicate Account,notbelong to,OK to submit");
- str.append("#@#Acct Delete,Yes,Xiamen MunicipalGovernment,,,38333226,0013000000ClAUs,,,,,,,,,,,,,,xigeng,CH_S_ENT_FJ_PS_TMxigeng,Enterprise,Duplicate Account,notbelong to,OK to submit");
- str.append("#@#Acct Delete,Yes,Xiamen MunicipalGovernment,,,42112821,0013000000D1GAJ,,,,,,,,,,,,,,xigeng,CH_S_ENT_FJ_PS_TMxigeng,Enterprise,Duplicate Account,notbelong to,OK to submit");
- str.append("#@#Acct Delete,Yes,Wal-Mart BusinessInformation Corporation Limited,,,2749753,,,,,,,,,,,,,,,lilzhong,CH_S_ENT_SZ_LENT_TMlilzhong,Enterprise,Duplicate Account,notbelong to,FAILED: SFDC Account IDcan't be blank.");
- str.append("#@#Acct Delete,Yes,China CTS Co.,Ltd,,,,,,,,,,,,,,,,,,hodu,CH_S_COMM_SZ_TAM_TMhodu,Commercial Mid-MarketTAM,Duplicate Account,Duplicate Accountwith China National Travel Service Group Corporation, Account ID:0013000000JFNYk.,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,HASBRO TOYS SHENZHENCOMPANY LTD,孩之宝玩具(深圳)有限公司,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,Thisacct. is duplicate with CH COMM SOUTH SISAM 02's HASBRO TOYS, which has nochinese name too.,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,Xunlei companyLimited,迅雷 ,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with CH_COMM_SZX_ISAM_03's xunlei,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,Xunlei companyLimited,迅雷 ,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with CH_COMM_SZX_ISAM_03's xunlei,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,OOCL corp.,东方海外货柜航运(中国)有限公司深圳分公司,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with the acct.东方海外货柜航运(中国)有限公司 of zeliu of south comm.a,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,HASBRO TOYS SHENZHENCOMPANY LTD,孩之宝玩具(深圳)有限公司,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,Thisacct. is duplicate with CH COMM SOUTH SISAM 02's HASBRO TOYS, which has nochinese name too.,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- str.append("#@#Acct Delete,Yes,OOCL corp.,东方海外货柜航运(中国)有限公司深圳分公司,,,,,,,,,,,,,,,,,yiqyu,CH_S_COMM_SZ_TAM_TMyiqyu,Commercial Mid-MarketTAM,Duplicate Account,This acct. isduplicate with the acct.东方海外货柜航运(中国)有限公司 of zeliu of south comm.a,FAILED:CSC ID can't be blank. SFDC Account ID can't be blank.");
- String t1 = str.toString();
- String t2[] = t1.split("#@#");
- String result[][] = new String[t2.length][30];
- for(int i = 0 ; i < t2.length ; i ++){
- result[i] = t2[i].split(",");
- }
- for(int i = 0 ; i < result.length ; i++){
- HSSFRow row = s.createRow(i);
- for(int j = 0 ; j < result[i].length ; j++){
- HSSFCell cell = row.createCell((short) j);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(result[i][j]);
- }
- }
- try {
- fos = new FileOutputStream("d://test.xls");
- wb.write(fos);
- fos.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- /**
- * 两个有用的属性:<br>
- * int rowsnum = sheet.getPhysicalNumberOfRows(); //返回当前sheetr的有效行数
- * int cellsnum = sheet.getRow(sheet.getFirstRowNum()).getPhysicalNumberOfCells();//返回当前行的有效列数
- *
- * @param url 读取指定的xls文件(当前可以是:d://test.xls),注意,要把路径中的 / 替换为 // 哦
- * @return 单个字符串(cell),可以是一维的(row),可以是二维的(sheet),可以是三维的(book)呵呵,自己定吧
- */
- private static String read(String url) {
- POIFSFileSystem fs = null;
- HSSFWorkbook wb = null;
- try {
- fs = new POIFSFileSystem(new FileInputStream(url));
- wb = new HSSFWorkbook(fs);
- } catch (IOException e) {
- e.printStackTrace();
- }
- HSSFSheet sheet = wb.getSheetAt(0);
- HSSFRow row = sheet.getRow(0);
- HSSFCell cell = row.getCell((short) 0);
- String msg = cell.getStringCellValue();
- return msg;
- }
- }