package com.vci.client.common.excel;
|
|
import java.io.BufferedInputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileNotFoundException;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.Date;
|
import java.util.List;
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import com.vci.corba.common.VCIError;
|
|
public class ExcelDocumentUtils {
|
/**
|
* 写回到excel文档
|
*
|
* @param sheetDataSet
|
* <P>
|
* 数据集合
|
* @param path
|
* <P>
|
* excel文档的路径
|
* @param name
|
* <P>
|
* excel文档的名称
|
* @throws VCIError
|
* @throws IOException
|
*/
|
public static void writeExcelDocument(String path, String name, List<SheetDataSet> sheetDataSet)
|
throws VCIError, IOException {
|
// 打开工作簿
|
Workbook workBook = makeWorkbook(name);
|
// sheet
|
for (int sheetIndex = 0; sheetIndex < sheetDataSet.size(); sheetIndex++) {
|
// 创建sheet
|
Sheet sheet = workBook.createSheet();
|
SheetDataSet dataSet = sheetDataSet.get(sheetIndex);
|
// 设置sheet名称
|
workBook.setSheetName(sheetIndex, dataSet.getSheet());
|
|
List<String[]> rowDataSet = dataSet.getDataSet();
|
// 遍历
|
for (int rowIndex = 0; rowIndex < rowDataSet.size(); rowIndex++) {
|
// ��row
|
Row row = sheet.getRow(rowIndex);
|
if (row == null) {
|
row = sheet.createRow(rowIndex);
|
}
|
String[] rowData = rowDataSet.get(rowIndex);
|
// row
|
for (int columnIndex = 0; columnIndex < rowData.length; columnIndex++) {
|
// cell
|
Cell cell = row.getCell(columnIndex);
|
if (cell == null) {
|
cell = row.createCell(columnIndex);
|
}
|
// 赋ֵ
|
cell.setCellValue(rowData[columnIndex]);
|
}
|
}
|
} // end
|
FileOutputStream fileOutputStream = makeFileOutputStream(path, name);
|
workBook.write(fileOutputStream);
|
fileOutputStream.close();
|
}
|
|
public static void writeExcelDocument(String path, String name, SheetDataSet dataSet) throws VCIError, IOException {
|
// 打开工作簿
|
Workbook workBook = makeWorkbook(name);
|
// sheet
|
|
// 创建sheet
|
Sheet sheet = workBook.createSheet();
|
// 设置sheet名称
|
workBook.setSheetName(0, dataSet.getSheet());
|
|
List<String[]> rowDataSet = dataSet.getDataSet();
|
// 遍历
|
for (int rowIndex = 0; rowIndex < rowDataSet.size(); rowIndex++) {
|
// ��row
|
Row row = sheet.getRow(rowIndex);
|
if (row == null) {
|
row = sheet.createRow(rowIndex);
|
}
|
String[] rowData = rowDataSet.get(rowIndex);
|
// row
|
for (int columnIndex = 0; columnIndex < rowData.length; columnIndex++) {
|
// cell
|
Cell cell = row.getCell(columnIndex);
|
if (cell == null) {
|
cell = row.createCell(columnIndex);
|
}
|
// 赋ֵ
|
cell.setCellValue(rowData[columnIndex]);
|
}
|
}
|
|
FileOutputStream fileOutputStream = makeFileOutputStream(path, name);
|
workBook.write(fileOutputStream);
|
fileOutputStream.close();
|
}
|
|
public static List<SheetDataSet> readExcelDocument(String name, InputStream fileInputStream)
|
throws VCIError, IOException {
|
return readExcelDocument(name, fileInputStream, false);
|
}
|
|
/**
|
* 读取excel文档的数据
|
*
|
* @param FileInputStream
|
* <P>
|
* excel文档
|
* @param name
|
* <P>
|
* excel文档的名称
|
* @throws IOException
|
*/
|
public static List<SheetDataSet> readExcelDocument(String name, InputStream fileInputStream, boolean isDelNullRow)
|
throws VCIError, IOException {
|
InputStream bufferInputStream = getBufferInputStream(fileInputStream);
|
// 打开工作簿
|
Workbook workBook = getWorkbook(fileInputStream, bufferInputStream, name);
|
// row
|
int maxRow = 0;
|
List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
|
for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
|
Sheet sheet = workBook.getSheetAt(sheetIndex);
|
SheetDataSet sheetDataSet = new SheetDataSet();
|
sheetDataSet.setSheet(sheet.getSheetName());
|
dataSet.add(sheetDataSet);
|
// 遍历
|
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
|
Row rowDataSet = sheet.getRow(rowIndex);
|
|
// sheet
|
if (rowDataSet == null) {
|
if (isDelNullRow) {
|
sheetDataSet.getDataSet().add(new String[0]);
|
}
|
continue;
|
}
|
int currentcolumnSize = rowDataSet.getLastCellNum();
|
if (maxRow < currentcolumnSize) {
|
maxRow = currentcolumnSize;
|
}
|
//
|
String[] values = new String[maxRow];
|
Arrays.fill(values, "");
|
boolean hasValue = false;
|
// 遍历
|
for (int columnIndex = 0; columnIndex < rowDataSet.getLastCellNum(); columnIndex++) {
|
String value = getCellValue(columnIndex, rowDataSet);
|
values[columnIndex] = rightTrim(value);
|
hasValue = true;
|
}
|
if (hasValue) {
|
sheetDataSet.getDataSet().add(values);
|
}
|
}
|
}
|
bufferInputStream.close();
|
return dataSet;
|
}
|
|
/**
|
* 读取excel文档的数据
|
*
|
* @param path
|
* <P>
|
* excel文档的路径
|
* @param name
|
* <P>
|
* excel文档的名称
|
* @throws IOException
|
*/
|
public static List<SheetDataSet> readExcelDocument(String path, String name) throws VCIError, IOException {
|
FileInputStream fileInputStream = getFileInputStream(path, name);
|
InputStream bufferInputStream = getBufferInputStream(fileInputStream);
|
// 打开工作簿
|
Workbook workBook = getWorkbook(fileInputStream, bufferInputStream, name);
|
// row
|
int maxRow = 0;
|
List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
|
for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
|
Sheet sheet = workBook.getSheetAt(sheetIndex);
|
SheetDataSet sheetDataSet = new SheetDataSet();
|
sheetDataSet.setSheet(sheet.getSheetName());
|
dataSet.add(sheetDataSet);
|
// 遍历
|
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
|
Row rowDataSet = sheet.getRow(rowIndex);
|
|
// sheet
|
if (rowDataSet == null) {
|
continue;
|
}
|
int currentcolumnSize = rowDataSet.getLastCellNum();
|
//
|
if (maxRow < currentcolumnSize) {
|
maxRow = currentcolumnSize;
|
}
|
String[] values = new String[maxRow];
|
Arrays.fill(values, "");
|
boolean hasValue = false;
|
// 遍历
|
for (int columnIndex = 0; columnIndex < rowDataSet.getLastCellNum(); columnIndex++) {
|
String value = getCellValue(columnIndex, rowDataSet);
|
if (columnIndex == 0 && value.trim().equals("")) {
|
break;
|
}
|
values[columnIndex] = rightTrim(value);
|
hasValue = true;
|
}
|
if (hasValue) {
|
sheetDataSet.getDataSet().add(values);
|
}
|
}
|
}
|
bufferInputStream.close();
|
return dataSet;
|
}
|
|
private static Workbook makeWorkbook(String name) throws VCIError {
|
|
// 创建
|
Workbook workBook = null;
|
if (getSuffix(name).equals(".xls")) {
|
workBook = new HSSFWorkbook();
|
} else if (getSuffix(name).equals(".xlsx")) {
|
workBook = new XSSFWorkbook();
|
}
|
return workBook;
|
|
}
|
|
private static FileOutputStream makeFileOutputStream(String path, String name)
|
throws VCIError, FileNotFoundException {
|
FileOutputStream fileOutputStream = new FileOutputStream(path + "\\" + name);
|
return fileOutputStream;
|
}
|
|
private static FileInputStream getFileInputStream(String path, String name) throws VCIError, FileNotFoundException {
|
File xlsFile = new File(path + "\\" + name);
|
FileInputStream fileInputStream = new FileInputStream(xlsFile);
|
return fileInputStream;
|
}
|
|
private static BufferedInputStream getBufferInputStream(InputStream fileInputStream) throws VCIError {
|
BufferedInputStream bufferInputStream = new BufferedInputStream(fileInputStream);
|
return bufferInputStream;
|
}
|
|
/**
|
* 读取单元格的值
|
*/
|
private static String getCellValue(int column, Row row) {
|
return getCellValue(row.getCell(column));
|
}
|
|
public static String getCellValue(Cell cell) {
|
String cellValueString = "";
|
if (cell == null)
|
return cellValueString;
|
int cellType = cell.getCellType();
|
SimpleDateFormat sdfDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
// add by xchao 2012.11.22 处理日期类型和Cell
|
if (cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
|
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
|
cellValueString = sdfDateTime.format(date);
|
} else if (cellType == Cell.CELL_TYPE_BLANK) {
|
cellValueString = "";
|
} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
|
cellValueString = cell.getBooleanCellValue() ? "1" : "0";
|
} else if (cellType == Cell.CELL_TYPE_ERROR) {
|
cellValueString = "";
|
} else if (cellType == Cell.CELL_TYPE_FORMULA) {
|
try {
|
cellValueString = String.valueOf(cell.getNumericCellValue());
|
} catch (Exception e) {
|
cellValueString = cell.getRichStringCellValue().getString();
|
}
|
} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
|
// String[] dataFormatStrings = cell.getCellStyle().getDataFormatString().replace("_", "").trim().split("\\.");
|
// int maxFracDigits = 0;
|
// if(dataFormatStrings.length == 2){
|
// maxFracDigits = dataFormatStrings[1].length();
|
// numFormat.setMaximumFractionDigits(maxFracDigits);
|
// cellValueString = numFormat.format(cell.getNumericCellValue()).replace(",", "").trim();
|
// } else {
|
// cellValueString = cell.toString();
|
// }
|
// cell.setCellType(Cell.CELL_TYPE_STRING);
|
// cellValueString = cell.getStringCellValue();
|
DecimalFormat df = new DecimalFormat("#.#########");
|
cellValueString = df.format(cell.getNumericCellValue());
|
} else if (cellType == Cell.CELL_TYPE_STRING) {
|
// 移除数据列中前可在存在的"'"
|
String val = cell.getStringCellValue();
|
while (val.startsWith("'")) {
|
val = val.substring(1);
|
}
|
cellValueString = val;
|
}
|
return cellValueString;
|
}
|
|
private static Workbook getWorkbook(InputStream fileInputStream, InputStream bufferInputStream, String name)
|
throws VCIError, IOException {
|
|
// 打开工作簿
|
Workbook workBook = null;
|
if (getSuffix(name).equals(".xls")) {
|
POIFSFileSystem fileSystem = new POIFSFileSystem(bufferInputStream);
|
workBook = new HSSFWorkbook(fileSystem);
|
} else if (getSuffix(name).equals(".xlsx")) {
|
workBook = new XSSFWorkbook(fileInputStream);
|
}
|
return workBook;
|
|
}
|
|
private static String getSuffix(String name) {
|
int index = name.lastIndexOf(".");
|
return name.substring(index, (name.length()));
|
}
|
|
/**
|
* ȥ���ַ��ұߵĿո�
|
*/
|
private static String rightTrim(String value) {
|
if (value == null) {
|
return "";
|
}
|
int length = value.length();
|
for (int i = length - 1; i >= 0; i--) {
|
if (value.charAt(i) != 0x20) {
|
break;
|
}
|
length--;
|
}
|
return value.substring(0, length);
|
}
|
|
public static void main(String args[]) throws IOException {
|
try {
|
List<SheetDataSet> sheetDataSet = readExcelDocument("D:\\", "a.xlsx");
|
writeExcelDocument("D:\\", "b.xlsx", sheetDataSet);
|
} catch (VCIError e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
|
}
|
