java读取Excel文件的数据组成insert sql并输出txt

···java
package com.asu;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

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 org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelToSql {

private String fileName = null;
private InputStream excelis = null;
private HSSFSheet sheet = null;
private XSSFSheet sheetXs = null;
private File f = null;
int maxcolnum = 0;
Integer startLine = 0;
String sql = null;

public static void main(String args[]) throws Exception {
	String inPath = "C:\\Users\\Administrator\\Desktop\\a.xlsx";// Excel文件的路径和名称
	String outPath = "C:\\Users\\Administrator\\Desktop";// 输出SQL文件的路径
	String outName = "a";// 输出SQL文件的名称
	System.out.println("正在处理数据......");
	ExcelToSql excelToSql = new ExcelToSql(inPath);
	excelToSql.setMaxcolnum(17);// 最大的列位置
	excelToSql.setStartLine(1);// 开始行数，为页面的行数
	excelToSql.setSql("insert into tr_xtreward(pk_group,pk_org,creationtime,seailno,begindate,enddate,prjcode,def1,deptcode,rewardmoney,pk_billtype,systemcode,pk_reward,ts,dr,batchno,pagenum) values(");
	ArrayList<ArrayList> fileData = excelToSql.getFileData();
	String result = excelToSql.dealData(fileData);
	writeToFile(outPath, outName, ".sql", result);
	System.out.println("导出成功");
}

/**
 * 构造方法
 * 
 * @param fileName
 */
public ExcelToSql(String fileName) {
	this.fileName = fileName;
}

/**
 * 将数据组成SQL
 * 
 * @param fileData
 * @return
 */
public String dealData(ArrayList<ArrayList> fileData) {
	String result = "";
	for (int i = 0; i < fileData.size(); i++) {
		ArrayList lineList = fileData.get(i);
		String sql_line_begin = getSql();
		String sql_line_end = ");";
		for (int j = 0; j < lineList.size(); j++) {
			String object = (String) lineList.get(j);
			if (j != lineList.size() - 1) {
				sql_line_begin = sql_line_begin + "'" + object + "',";
			} else {
				sql_line_begin = sql_line_begin + "'" + object + "'" + sql_line_end;
			}
		}
		result = result + sql_line_begin + "\n";
	}
	return result;
}

/**
 * 将文件写入到本地磁盘
 *
 * @param dirPath  目录路径
 * @param fileName 文件名，不包含文件后缀
 * @param fileType 文件后缀
 * @param buffer   文件
 * @return 文件路径
 */
public static String writeToFile(String dirPath, String fileName, String fileType, String buffer) {
	SimpleDateFormat df = new SimpleDateFormat("_yyyy-MM-dd-HH-mm-ss");
	String format = df.format(new Date());
	String yearMonth = format.substring(1, 11);
	dirPath = dirPath + File.separator + yearMonth;
	File file = new File(dirPath);
	if (!file.exists()) {
		file.mkdirs();
	}

	String fileFullName = fileName + format + fileType;
	String filePath = dirPath + File.separator + fileFullName;
	Writer writer = null;
	try {
		OutputStream out = new FileOutputStream(filePath);
		writer = new java.io.OutputStreamWriter(out, "UTF-8");// UTF-8,GB2312
		writer.write(buffer.toString());
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		try {
			writer.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	return filePath;
}

/**
 * 读取Excel数据
 * 
 * @return
 * @throws Exception
 */
public ArrayList<ArrayList> getFileData() throws Exception {
	if (openExcel()) {
		ArrayList<ArrayList> lineList = new ArrayList<ArrayList>();

// int maxcolnum = 18; //modify 最大的列位置
// Integer startLine = 1;//modify 开始行数，为页面的行数

		int row = 0;// 添加导入失败原因
		int column = 0;// 添加导入失败原因

		try {
			for (int i = getStartLine();; i++) {// 行
				row = i + 1;
				ArrayList<String> valList = new ArrayList<String>();
				int index = 0;
				for (int j = 0; j < getMaxcolnum(); j++) {// 列
					// 按照顺序读取
					Object value = getExcelAt(i, j);// 读文件的第i行，第n列
					String result = value == null ? null : ((String) value).trim();

					// add by asu 数字转字符串带小数点问题，如1 ————> 1.0
					if (j == 8 || j == 14 || j == 16) {// 读取j列时做处理
						String[] split = result.split("\\.");
						System.out.println("数字转字符串带小数点问题处理:" + result + "————>" + split[0]);
						result = split[0];
					} // end

					valList.add(result);
					if (result == null) {
						index++;
					}
				}
				if (index == getMaxcolnum()) {// 如果excel某一行的所有有效列都为空，则读取完毕
					break;
				}
				lineList.add(valList);
			}
		} catch (Exception e) {

		}

		excelis.close();
		return lineList;
	}
	return null;
}

public Object getExcelAt(int row, int col) throws Exception {
	Object obj = null;
	if (sheet == null && sheetXs == null) {
		throw new Exception("尚未打开Excel文件");
	} else {
		if (sheet != null) {
			HSSFRow hssfrow = sheet.getRow(row);

			if (hssfrow == null)
				return null;
			HSSFCell cell = hssfrow.getCell((short) col);
			if (cell == null)
				return null;
			switch (cell.getCellType()) {
			case STRING:
				obj = cell.getStringCellValue();
				break;
			case BLANK:
				obj = null;
				break;
			case BOOLEAN:
				obj = new Boolean(cell.getBooleanCellValue());
				break;
			case ERROR:
				obj = Byte.valueOf(cell.getErrorCellValue());
				break;
			case FORMULA:
				obj = new Double(cell.getNumericCellValue()).toString();
				break;
			case NUMERIC:
				obj = new Double(cell.getNumericCellValue()).toString();
				break;
			}
		} else if (sheetXs != null) {
			XSSFRow xshssfrow = sheetXs.getRow(row);
			if (xshssfrow == null)
				return null;
			XSSFCell cell = xshssfrow.getCell((short) col);
			if (cell == null)
				return null;
			switch (cell.getCellType()) {
			case STRING:
				obj = cell.getStringCellValue();
				break;
			case BLANK:
				obj = null;
				break;
			case BOOLEAN:
				obj = new Boolean(cell.getBooleanCellValue());
				break;
			case ERROR:
				obj = Byte.valueOf(cell.getErrorCellValue());
				break;
			case FORMULA:
				obj = new Double(cell.getNumericCellValue()).toString();
				break;
			case NUMERIC:
				obj = new Double(cell.getNumericCellValue()).toString();
				break;
			}
		}
	}
	return obj;
}

public boolean openExcel() throws Exception {
	f = new File(fileName);
	excelis = new FileInputStream(f);
	if (fileName.endsWith(".xls")) {
		POIFSFileSystem fs = new POIFSFileSystem(excelis);
		HSSFWorkbook workbook = new HSSFWorkbook(fs);
		sheet = workbook.getSheetAt(0);
	} else {
		XSSFWorkbook workbook = new XSSFWorkbook(excelis);
		sheetXs = workbook.getSheetAt(0);
	}

	// sheet = workbook.getSheetAt(0);

	return true;
}

public int getMaxcolnum() {
	return maxcolnum;
}

public void setMaxcolnum(int maxcolnum) {
	this.maxcolnum = maxcolnum;
}

public Integer getStartLine() {
	return startLine;
}

public void setStartLine(Integer startLine) {
	this.startLine = startLine;
}

public String getSql() {
	return sql;
}

public void setSql(String sql) {
	this.sql = sql;
}

}