读取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 + "\r\n"); } 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(); } } }
