package com.cn.peitest.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.PrintStream;

import java.text.DecimalFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;

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.poifs.filesystem.POIFSFileSystem;

import com.cn.peitest.excel.tool.ExcelPoiTools;

public class pei_testXLS {

	/*

	 * 处理xls文件

	 * */

	public static void main(String[] args){

		try {

			//生成111.txt文件的地址

			PrintStream ps = new PrintStream("C:\\Users\\pei\\Desktop\\111.txt");

			//设置将内容打印到111.txt文件中

			//System.setOut(ps);

			//要打印的内容拼成sql文件

			System.out.println("insert into `t_pub_bfshlp` values" );

		} catch (FileNotFoundException e1) {

			// TODO Auto-generated catch block

			e1.printStackTrace();

		}

		try {

			HSSFWorkbook rwb = null;

			HSSFSheet incomeSheet = null;

			POIFSFileSystem fs = null;

			HSSFRow row = null;

			int l = 0;

			//要读取的文件路径

			String tableFile = "C:\\Users\\Pei\\Desktop\\HYD test 1_0_2 tm=2020_09_29_09_30.xls";

			//读取excle时这句话是固定用法

			fs = new POIFSFileSystem(new FileInputStream(new File(tableFile)));

			rwb = new HSSFWorkbook(fs);

			// 获取到Excel中的Sheet

			incomeSheet = rwb.getSheetAt(1);

			StringBuffer date=new StringBuffer("");

			for (int x = 5; x <= 314; x++) {//读取多少行

				System.out.print("('99'," );

				row = incomeSheet.getRow(x);

				// 读取格 编号

				for (int y = 0; y <= 4; y++) {//读取每行读多少列,获取前5列的值分别取前两位

					l++;

					HSSFCell cell = row.getCell(y);

					if(cell!=null&&!ExcelPoiTools.getStringCellValue(cell).equals("")&&ExcelPoiTools.getStringCellValue(cell)!=null){		

						date =date.append( ExcelPoiTools.getStringCellValue(cell).substring(0,2));//获取每列值的前两位数

					}

				}

				l=(date.length())/2;

				System.out.print("'PRD_IND_TYP"+""+"',");

				System.out.print("'" + date+"',");

				date.setLength(0);

				// 读取格 内容

				for (int y = 5; y <= 5; y++) {//从第5列开始读取后面每列值得内容

					HSSFCell cell = row.getCell(y);

					if(cell!=null){

					date =date.append( ExcelPoiTools.getStringCellValue(cell));

					}

				}

				System.out.println("'" + date+"','','','20171110010101','','','','',''),");

				date.setLength(0);

			}

		} catch (Exception e) {

			System.out.println(e);

		}

	}

	//处理数据

	 public static String getStringCellValue(HSSFCell cell) {

	        String strCell = "";

	        switch (cell.getCellType()) {

	            case HSSFCell.CELL_TYPE_STRING://字符串

	                strCell = cell.getStringCellValue();

	                break;

	            case HSSFCell.CELL_TYPE_NUMERIC:

	                DecimalFormat df = new DecimalFormat("0.00");//处理数字

	                strCell = df.format(cell.getNumericCellValue());

	                break;

	            case HSSFCell.CELL_TYPE_BOOLEAN:

	                strCell = String.valueOf(cell.getBooleanCellValue());

	                break;

	            case HSSFCell.CELL_TYPE_BLANK://空值

	                strCell = "";

	                break;

	            default:

	                strCell = "";

	                break;

	        }

	        if ("".equals(strCell) || strCell == null) {

	            return "";

	        }

	        if (cell == null) {

	            return "";

	        }

	        return strCell;

	    }

}

//pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>cn.com</groupId>

  <artifactId>excelReadAndWrite</artifactId>

  <version>0.0.1-SNAPSHOT</version>

   <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>3.8.1</version>

      <scope>test</scope>

    </dependency>

	 <dependency>

	    <groupId>org.apache.directory.studio</groupId>

	    <artifactId>org.apache.commons.codec</artifactId>

	    <version>1.8</version>

	</dependency>

	 <dependency>

		<groupId>net.sourceforge.jexcelapi</groupId>

		<artifactId>jxl</artifactId>

		<version>2.6.12</version>

	</dependency>

	<dependency>

		<groupId>org.apache.poi</groupId>

		<artifactId>poi-ooxml</artifactId>

		<version>3.9</version>

	</dependency>

  </dependencies>

</project>