readzyng.java

PIO读写EXCEL实例

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.*;

public class ReadZYng{
	public static String fileToBeRead="C:/汇总.xls";
	public static String outputFile ="C:/专用发票审核结果.xls";
	public static void main(String argv[]){ 
	try{
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
		HSSFSheet sheet = workbook.getSheet("南");   
      
	   //读取表一  
	   //读取第一行数据
		HSSFRow row3 = sheet.getRow(4);
		HSSFCell cell3 = row3.getCell((short)1);
	    double ZYZFBY = cell3.getNumericCellValue();
		HSSFRow row4 = sheet.getRow(4);
		HSSFCell cell4 = row4.getCell((short)2);
		double ZYZFLJ = cell4.getNumericCellValue();

		HSSFRow row5 = sheet.getRow(4);
		HSSFCell cell5 = row5.getCell((short)3);
	    double ZYQLBY = cell5.getNumericCellValue();
		HSSFRow row6 = sheet.getRow(4);
		HSSFCell cell6 = row6.getCell((short)4);
		double ZYQLLJ = cell6.getNumericCellValue();

		HSSFRow row7 = sheet.getRow(4);
		HSSFCell cell7 = row7.getCell((short)5);
	    double ZYBFBY = cell7.getNumericCellValue();
		HSSFRow row8 = sheet.getRow(4);
		HSSFCell cell8 = row8.getCell((short)6);
		double ZYBFLJ = cell8.getNumericCellValue();

		HSSFRow row9 = sheet.getRow(4);
		HSSFCell cell9 = row9.getCell((short)7);
	    double ZYHJBY = cell9.getNumericCellValue();
		HSSFRow row10 = sheet.getRow(4);
		HSSFCell cell10 = row10.getCell((short)8);
		double ZYHJLJ = cell10.getNumericCellValue();

       //读取第二行数据
		HSSFRow row11 = sheet.getRow(5);
		HSSFCell cell11 = row11.getCell((short)1);
	    double ZYZFBY2 = cell11.getNumericCellValue();
		HSSFRow row12 = sheet.getRow(5);
		HSSFCell cell12 = row12.getCell((short)2);
		double ZYZFLJ2 = cell12.getNumericCellValue();

		HSSFRow row13 = sheet.getRow(5);
		HSSFCell cell13 = row13.getCell((short)3);
	    double ZYQLBY2 = cell13.getNumericCellValue();
		HSSFRow row14 = sheet.getRow(5);
		HSSFCell cell14 = row14.getCell((short)4);
		double ZYQLLJ2 = cell14.getNumericCellValue();

		HSSFRow row15 = sheet.getRow(5);
		HSSFCell cell15 = row15.getCell((short)5);
	    double ZYBFBY2 = cell15.getNumericCellValue();
		HSSFRow row16 = sheet.getRow(5);
		HSSFCell cell16 = row16.getCell((short)6);
		double ZYBFLJ2 = cell16.getNumericCellValue();

		HSSFRow row17 = sheet.getRow(5);
		HSSFCell cell17 = row17.getCell((short)7);
	    double ZYHJBY2 = cell17.getNumericCellValue();
		HSSFRow row18 = sheet.getRow(5);
		HSSFCell cell18 = row18.getCell((short)8);
		double ZYHJLJ2 = cell18.getNumericCellValue();


		//读取第三行数据
		HSSFRow row19 = sheet.getRow(6);
		HSSFCell cell19 = row19.getCell((short)1);
	    double ZYZFBY3 = cell19.getNumericCellValue();
		HSSFRow row20 = sheet.getRow(6);
		HSSFCell cell20 = row20.getCell((short)2);
		double ZYZFLJ3 = cell20.getNumericCellValue();

		HSSFRow row21 = sheet.getRow(6);
		HSSFCell cell21 = row21.getCell((short)3);
	    double ZYQLBY3 = cell21.getNumericCellValue();
		HSSFRow row22 = sheet.getRow(6);
		HSSFCell cell22 = row22.getCell((short)4);
		double ZYQLLJ3 = cell22.getNumericCellValue();

		HSSFRow row23 = sheet.getRow(6);
		HSSFCell cell23 = row23.getCell((short)5);
	    double ZYBFBY3 = cell23.getNumericCellValue();
		HSSFRow row24 = sheet.getRow(6);
		HSSFCell cell24 = row24.getCell((short)6);
		double ZYBFLJ3 = cell24.getNumericCellValue();

		HSSFRow row25 = sheet.getRow(6);
		HSSFCell cell25 = row25.getCell((short)7);
	    double ZYHJBY3 = cell25.getNumericCellValue();
		HSSFRow row26 = sheet.getRow(6);
		HSSFCell cell26 = row26.getCell((short)8);
		double ZYHJLJ3 = cell26.getNumericCellValue();

		
	   try {
			// 创建新的Excel 工作簿
		    HSSFWorkbook workbookwt = new HSSFWorkbook();
			// 在Excel工作簿中建一工作表，其名为缺省值
			// 如要新建一名为"效益指标"的工作表，其语句为：
			// HSSFSheet sheet = workbook.createSheet("效益指标");
			HSSFSheet sheetwt = workbookwt.createSheet("1-2");
			// 在索引0的位置创建行（最顶端的行）
			HSSFRow rowwt11 = sheetwt.createRow((short) 0);
			HSSFCell cellwt11 = rowwt11.createCell((short) 0);
			//在索引0的位置创建单元格（左上端）
			// 定义单元格为字符串类型
			cellwt11.setEncoding(HSSFCell.ENCODING_UTF_16);
			HSSFFont font = workbookwt.createFont();
			font.setFontHeightInPoints((short)10);
			//font.setFontWidthInPoints((short)124);
			font.setFontName("宋体");
			font.setColor(HSSFFont.COLOR_RED);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			HSSFCellStyle cellStyle= workbookwt.createCellStyle();
			cellStyle.setFont(font);
			cellwt11.setCellStyle(cellStyle);
		   // cellwt.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格中输入一些内容
			if (ZYHJBY!=SJBYHJ)
			{
					cellwt11.setCellValue("表一‘录入发票错误'本月合计数:"+ZYHJBY+"不等于本月三项相加的和:"+SJBYHJ+'('+ZYBFBY+'+'+ZYQLBY+'+'+ZYBFBY+')');
			}
		    if (ZYHJLJ!=SJLJHJ)
			{
            HSSFRow rowwt12 = sheetwt.createRow((short) 1);
			HSSFCell cellwt12 = rowwt12.createCell((short) 0);
			//在索引0的位置创建单元格（左上端）
			// 定义单元格为字符串类型
			cellwt12.setEncoding(HSSFCell.ENCODING_UTF_16);
			cellwt12.setCellStyle(cellStyle);
		   // cellwt.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格中输入一些内容
				  cellwt12.setCellValue("表一‘录入发票错误'累计合计数:"+ZYHJLJ+"不等于累计三项相加的和:"+SJLJHJ+'('+ZYBFLJ+'+'+ZYQLLJ+'+'+ZYBFLJ+')');
			}
			
			if (ZYZFLJ4!=SJZYZFLJ4)
			{
		    HSSFRow rowwt43 = sheetwt.createRow((short) 7);
			HSSFCell cellwt43 = rowwt43.createCell((short) 0);
			//在索引0的位置创建单元格（左上端）
			// 定义单元格为字符串类型
			cellwt43.setEncoding(HSSFCell.ENCODING_UTF_16);
			cellwt43.setCellStyle(cellStyle);
		   // cellwt.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格中输入一些内容

					cellwt43.setCellValue("表一‘正常发票'的'属于作废发票份数'累计数:"+ZYZFLJ4+"不等于本月数加上期累计的和:"+SJZYZFLJ4+'('+ZYZFBY4+'+'+SYZYZFLJ4+')');
			}
			
            {
				cellwt116.setCellValue("表二‘本期需审核核查发票总数'的'缺联发票'合计数:"+ZYSHQL+"不等于表二第2＋3＋4＋5＋6行相加的和:"+ZYSHQLZX1);
			}  
		    

     		 // 新建一输出文件流            
			FileOutputStream fOut = new FileOutputStream(outputFile);

			 // 把相应的Excel 工作簿存盘
		    workbookwt.write(fOut);
		    fOut.flush();			
            fOut.close();                                  // 操作结束，关闭文件
            System.out.println("生成审核结果EXCLE文件完成！");


         } catch (Exception e) {
			System.out.println("已运行 xlCreate() : "+e);
         }


    }catch(Exception e) {
       System.out.println("已运行xlRead() : "+e );
    }
  }
}