读取Excel并写入txt

package com.baoqilai.scp.util;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;


public class ExcelUtil {

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	public static List<Map<Integer, Object>> parseExcel(File excel) throws Exception {
		FileInputStream inputStream = new FileInputStream(excel);
		return parseExcel(inputStream);
	}
	
	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * @param sheet 要设置的sheet.
	 * @param textlist 下拉框显示的内容
	 * @param firstRow 开始行
	 * @param endRow 结束行
	 * @param firstCol   开始列
	 * @param endCol  结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
			String[] textlist, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
		sheet.addValidationData(data_validation_list);
		return sheet;
	}

	/**
	 * 解析Excel
	 * @param inputStream
	 *            文件
	 * @return List集合
	 */

	public static List<Map<Integer, Object>> parseExcel(InputStream inputStream) throws Exception {
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			HSSFSheet sheet = workbook.getSheetAt(0);
			int lastRowIndex = sheet.getLastRowNum();
			List<Map<Integer, Object>> excelData = new ArrayList<>();

			for (int i = 1; i <= lastRowIndex; i++) {
				HSSFRow row = sheet.getRow(i);
				Iterator<Cell> cells = row.cellIterator();
				Map<Integer, Object> rowData = new HashMap<>();
				while (cells.hasNext()) {
					Cell cell = cells.next();
					if(cell==null){
						continue;
					}
					Integer columnIndex = cell.getColumnIndex();
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 数值
//						if (DateUtil.isCellDateFormatted(cell)) {
//							rowData.put(columnIndex, cell.getDateCellValue());
//						} else {
//							rowData.put(columnIndex, cell.getNumericCellValue());
//						}
						String result = "";
						if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
							SimpleDateFormat sdf = null;
							if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
								sdf = new SimpleDateFormat("HH:mm");
							} else {// 日期
								sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
							}
							Date date = cell.getDateCellValue();
							result = sdf.format(date);
						} else if (cell.getCellStyle().getDataFormat() == 58) {
							// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
							double value = cell.getNumericCellValue();
							Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
							result = sdf.format(date);
						} else {
							if (cell != null) {
								cell.setCellType(Cell.CELL_TYPE_STRING);
							}
							result = cell.getStringCellValue();
						}
						rowData.put(columnIndex, result);
					} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 布尔
						rowData.put(columnIndex, cell.getBooleanCellValue());
					} else { // 字符串
						if (cell != null) {
							cell.setCellType(Cell.CELL_TYPE_STRING);
						}
						rowData.put(columnIndex, cell.getStringCellValue());
					}
				}
				excelData.add(rowData);
			}
			return excelData;
		}catch (OfficeXmlFileException e){
			logger.error("文件解析错误应该是xlsx===="+e.getMessage());
			return parseExcel2(inputStream);
		}

		// workbook.close();
	}

	public static List<Map<Integer, Object>> parseExcel2(MultipartFile excel) throws Exception {
		
		String fileName = excel.getOriginalFilename();
		if(fileName.matches("^.+\.(?i)(xls)$")){//2003
			return parseExcel(excel.getInputStream());
		}
		return parseExcel2(excel.getInputStream());
	}

	public static List<Map<Integer, Object>> parseExcel2(InputStream inputStream) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = workbook.getSheetAt(0);
		int lastRowIndex = sheet.getLastRowNum();
		List<Map<Integer, Object>> excelData = new ArrayList<>();

		for (int i = 1; i <= lastRowIndex; i++) {
			XSSFRow row = sheet.getRow(i);
			if (null == row)
				continue;
			Iterator<Cell> cells = row.cellIterator();
			Map<Integer, Object> rowData = new HashMap<>();
			while (cells.hasNext()) {
				Cell cell = cells.next();
				if(cell==null){
					continue;
				}
				Integer columnIndex = cell.getColumnIndex();
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 数值
//					if (DateUtil.isCellDateFormatted(cell)) {
//						rowData.put(columnIndex, cell.getDateCellValue());
//					} else {
//						rowData.put(columnIndex, cell.getNumericCellValue());
//					}
					String result = "";
					if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
						SimpleDateFormat sdf = null;
						if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
							sdf = new SimpleDateFormat("HH:mm");
						} else {// 日期
							sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						}
						Date date = cell.getDateCellValue();
						result = sdf.format(date);
					} else if (cell.getCellStyle().getDataFormat() == 58) {
						// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
						result = sdf.format(date);
					} else {
						if (cell != null) {
							cell.setCellType(Cell.CELL_TYPE_STRING);
						}
						result = cell.getStringCellValue();
					}
					rowData.put(columnIndex, result);
				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 布尔
					rowData.put(columnIndex, cell.getBooleanCellValue());
				} else { // 字符串
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
					}
					rowData.put(columnIndex, cell.getStringCellValue());
				}
			}
			excelData.add(rowData);
		}
		// workbook.close();
		return excelData;
	}
	
	/**
	 * desc: 导出excel表格 author: liuchenyu date: 2017/4/8 14:30
	 *
	 * @param titles
	 *            Excel表各列字段名
	 * @param sheetname
	 *            工作表标签名
	 * @param data
	 *            导出的数据源
	 * @param filename
	 *            导出的文件名
	 * @param response
	 * @param request
	 * @throws Exception
	 */
	public static void exportExcel(String[] titles, String sheetname, List<Map<String, Object>> data, String filename,
			ArrayList<String> list, HttpServletResponse response, HttpServletRequest request) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(sheetname);
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < titles.length; i++) {
			row.createCell(i).setCellValue(titles[i]);
		}

		for (int i = 0; i < data.size(); i++) {
			Map<String, Object> obj = data.get(i);
			row = sheet.createRow(i + 1);

			for (int j = 0; j < list.size(); j++) {
				String key = list.get(j);
				HSSFCell cell = row.createCell(j);

				cell.setCellValue(obj.get(key) == null ? "--" : obj.get(key) + "");

			}
		}

		// filename = new String(filename.getBytes("gbk-8"), "iso8859-1");
		String encoding = "utf-8";
		String userAgent = request.getHeader("user-agent");
		logger.info("userAgent: " + userAgent);
		if (userAgent.toLowerCase().indexOf("msie") != -1) {
			encoding = "gbk";
		}
		filename = new String(filename.getBytes(encoding), "iso8859-1");
		response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
		workbook.write(response.getOutputStream());
		// workbook.close();
	}

	public static void downloadExcel(HttpServletResponse response, Workbook workbook, String execelName) {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			workbook.write(os);
		} catch (IOException e) {
			logger.error("write data to ByteArrayOutputStream fail.", e);
		}

		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);

		// 设置response参数，可以打开下载页面
		// HttpServletResponse response = WebUtils.getResponse();
		// response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		ServletOutputStream out = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String format = sdf.format(new Date());
		try {
			response.setHeader("Content-Disposition",
					"attachment;filename=" + new String((execelName + format + ".xls").getBytes(), "iso-8859-1"));
			out = response.getOutputStream();
		} catch (Exception e1) {
			logger.error("write data to ServletOutputStream fail.", e1);
		}
		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		try {
			bis = new BufferedInputStream(is);
			bos = new BufferedOutputStream(out);

			byte[] buff = new byte[2048];
			int bytesRead;
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}

		} catch (final IOException e) {
			logger.error("write data to ServletOutputStream fail.", e);
		} finally {
			if (bis != null)
				try {
					bis.close();
				} catch (IOException e) {
					logger.error("close InputStream fail.", e);
				}
			if (bos != null)
				try {
					bos.close();
				} catch (IOException e) {
					logger.error("close OutputStream fail.", e);
				}
		}
	}

	public static void noDataExcel(HttpServletResponse response, String fileName) {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("1");
		Row row = sheet.createRow((short) 0);
		row.createCell(0).setCellValue("没有数据");
		row = sheet.createRow(1);
		row.createCell(0).setCellValue("没有找到数据 - - !!!!!");
		ExcelUtil.downloadExcel(response, wb, fileName);
	}
	
	
	

}

　　

package com.future.test;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.util.List;
import java.util.Map;

import com.baoqilai.scp.util.ExcelUtil;

public class addBiitemSql {
    static String imagesql = "D:\data\cc.txt";

    public static void method2(String file, String conent) {
        BufferedWriter out = null;
        try {
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, true)));
            out.write(conent + "
");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {

        String filePath = "C:/Users/Administrator/Desktop/bi.xlsx";
        File file = new File(filePath);
        try {

　　　　　　　　FileInputStream inputStream = new FileInputStream(file);

　　　　　　　　List<Map<Integer, Object>> itemList = ExcelUtil.parseExcel2(inputStream);

for (int i = 0; i < itemList.size(); i++) {
                 String itemName = itemList.get(i).get(0) == null ? "-1" : itemList.get(i).get(0).toString();
                 String cc="评价"+cc;
                 method2(imagesql, cc);
             }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }
}