一、建立Java Project 工程
思路:1.将excel 用POI解析出来
2.将获取的值拼成xml
二、创建lib文件夹,导入以下jar
三、代码如下
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.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 org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import com.util.HelpUtils;
public class XlsToXml {
public static void main(String[] args) {
try {
System.out.println(getTableSheetData("C:\\Users\\wdr\\Desktop\\excel转xml\\20170324.xls","C:\\Users\\wxs\\Desktop\\excel转xml\\wdxs.xml"));
System.out.print(getTableSheetData("C:\\Users\\wdr\\Desktop\\excel转xml\\Book.xlsx","C:\\Users\\wxs\\Desktop\\excel转xml\\wdx.xml"));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取表格的全部数据
*
* @param fileName
* @param storagePath
* @return
* @throws Exception
*/
public static boolean getTableSheetData(String fileName, String storagePath) throws Exception {
if (!HelpUtils.valid(fileName)) {
HelpUtils.print("文件名校验失败");
return false;
} else {
getExcelData(fileName, storagePath);
return true;
}
}
public static final String SUFFIX_XLS = "xls";
public static final String SUFFIX_XLSX = "xlsx";
/**
* 获得表格的列
*
* @param fileName
* @return
* @throws RuntimeException
*/
public static List<Map<String, Object>> getTableSheetCol(String fileName) throws RuntimeException {
if (!HelpUtils.valid(fileName))
return null;
else {
if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLS)) {
// 是excel2003?
return getXlsSheetItem(fileName);
}
if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLSX)) {
// 是excel2007以后
return getXlsxSheetItem(fileName);
} else {
HelpUtils.print("nonsupport file format");
HelpUtils.print("fileFormat : " + HelpUtils.getFileNameSuffix(fileName));
throw new RuntimeException("nonsupport file format, please check input fileName again");
}
}
}
/**
* getXlsSheetItem : 读取xls格式文件的所有sheet表的所有列名集合。
*
* @param fileName
* 文件名 默认表格格式规范,列名全部为字符串。
*/
private static List<Map<String, Object>> getXlsSheetItem(String fileName) {
TableObject tableObject = new TableObject(fileName).invoke();
HSSFWorkbook book = tableObject.getHssfBook();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
Sheet sheet = book.getSheetAt(i);
if (HelpUtils.ifSheetNullOrEmpty(sheet))
continue;
Row row = sheet.getRow(0);
if (HelpUtils.ifRowNullOrEmpty(row))
continue;
result.add(packageColsWithSheetName(book, i, row));
}
return result;
}
/**
* getXlsxSheetItem : 读取xlsx格式文件的所有sheet表的所有列名集合。
*
* @param fileName
* 文件名 默认表格格式规范,列名全部为字符串。 方法还可以进一步化简,可参考 getExcelData 进一步整合。
*/
private static List<Map<String, Object>> getXlsxSheetItem(String fileName) {
TableObject tableObject = new TableObject(fileName).invoke();
XSSFWorkbook book = tableObject.getXssfBook();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
Sheet sheet = book.getSheetAt(i);
if (HelpUtils.ifSheetNullOrEmpty(sheet))
continue;
Row row = sheet.getRow(0);
if (HelpUtils.ifRowNullOrEmpty(row))
continue;
result.add(packageColsWithSheetName(book, i, row));
}
return result;
}
private static Map<String, Object> packageColsWithSheetName(Workbook book, int i, Row row) {
Map<String, Object> map = new HashMap<String, Object>();
String sheet_name = book.getSheetName(i);
List<String> cols = getCols(row);
map.put("sheet_name", sheet_name);
map.put("cols", cols);
return map;
}
private static List<String> getCols(Row row) {
List<String> cols = new ArrayList<String>();
System.out.println(row.getLastCellNum());
for (int j = 0; j < row.getLastCellNum(); j++) {
Object obj = row.getCell(j);
cols.add(obj == null ? "" : obj.toString());
}
return cols;
}
/**
* 根据文件后缀格式,确定调用哪种Workbook,此处运用了多态,具体的解析操作都用的是接口。
*
* @param fileName
* @return
*/
private static void getExcelData(String fileName, String storagePath) throws Exception {
TableObject tableObject = new TableObject(fileName).invoke();
Workbook book = null;
// 创建一个XML的文件
Document docu = DocumentHelper.createDocument();
// 为这个XML文件设置一个根结点,名为:MultipleAudioInfoList
Element root = docu.addElement("MultipleAudioInfoList");
if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLS)) {
// 是excel2003?
book = tableObject.getHssfBook();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
// String type = HelpUtils.getFileNameSuffix(fileName);
FileInputStream inp = new FileInputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook(inp);
HSSFSheet sheet = wb.getSheetAt(i); // 获得第i个工作薄(2008工作薄)
// 判断该工作簿是否为空
if (HelpUtils.ifSheetNullOrEmpty(sheet))
continue;
// 获取excel标题 标题为第一行合并的内容
String excelTitle = HelpUtils.getMergedRegionValue(sheet, 0, 0);
System.out.println(excelTitle);
HSSFRow row2 = sheet.getRow(1); // 获得工作薄的第二行
if (HelpUtils.ifRowNullOrEmpty(row2))
continue;
//判断sheet的格式是否一致,是否为需要导成xml的sheet
if("类型名称".equals(HelpUtils.getCellValue(row2.getCell(0)))){
// 创建第一级结点,AudioInfo
Element audioInfo = null;
// 创建第二级节点 set
Element set = null;
// 在根目录下添加一个AudioInfo的子结点
audioInfo = root.addElement("AudioInfo");
// 在AudioInfo结点下添加相应的set子结点
set = audioInfo.addElement("set");
// 在set结点下添加相应的name子结点
Element name = set.addElement("name");
name.setText(HelpUtils.getCellValue(row2.getCell(1)));
// 在set结点下添加相应的programType子结点
Element type = set.addElement("type");
type.setText(HelpUtils.getCellValue(row2.getCell(4)));
// 在set结点下添加相应的director子结点
Element director = set.addElement("director");
director.setText(HelpUtils.getCellValue(row2.getCell(6)));
HSSFRow row3 = sheet.getRow(2); // 获得工作薄的第三行
if (HelpUtils.ifRowNullOrEmpty(row3))
continue;
// 在set结点下添加相应的orderName子结点
Element orderName = set.addElement("orderName");
orderName.setText(HelpUtils.getCellValue(row3.getCell(1)));
// 在set结点下添加相应的recorderNo子结点
Element orderNo = set.addElement("orderNo");
orderNo.setText(HelpUtils.getCellValue(row3.getCell(4)));
// 在set结点下添加相应的orderPhone子结点
Element orderPhone = set.addElement("orderPhone");
orderPhone.setText(HelpUtils.getCellValue(row3.getCell(6)));
HSSFRow row4 = sheet.getRow(3); // 获得工作薄的第四行
if (HelpUtils.ifRowNullOrEmpty(row4))
continue;
// 在set结点下添加相应的orderTime子结点
Element orderTime = set.addElement("orderTime");
orderTime.setText(HelpUtils.getCellValue(row4.getCell(1)));
// 在set结点下添加相应的orderAddress子结点
Element orderAddress = set.addElement("orderAddress");
orderAddress.setText(HelpUtils.getCellValue(row4.getCell(4)));
// 在set结点下添加相应的recordStandard子结点
Element orderStandard = set.addElement("orderStandard");
orderStandard.setText(HelpUtils.getCellValue(row4.getCell(6)));
HSSFRow row5 = sheet.getRow(4); // 获得工作薄的第五行
if (HelpUtils.ifRowNullOrEmpty(row5))
continue;
// 在set结点下添加相应的setName子结点
Element setName = set.addElement("setName");
setName.setText(HelpUtils.parseDate(row5.getCell(1)));
// 在set结点下添加相应的setType子结点
Element setType = set.addElement("setType");
setType.setText(HelpUtils.getCellValue(row5.getCell(4)));
// 在set结点下添加相应的recorderPhone子结点
Element setNo = set.addElement("setNo");
setNo.setText(HelpUtils.getCellValue(row5.getCell(6)));
HSSFRow row6 = sheet.getRow(5); // 获得工作薄的第六行
if (HelpUtils.ifRowNullOrEmpty(row6))
continue;
boolean isMerge6 = HelpUtils.isMergedRegion(sheet, 5, 1);
// 在set结点下添加相应的trackCount子结点
Element trackCount = set.addElement("trackCount");
if (isMerge6) {
trackCount.setText(HelpUtils.getMergedRegionValue(sheet, 5, 1));
}
HSSFRow row7 = sheet.getRow(6); // 获得工作薄的第七行
if (HelpUtils.ifRowNullOrEmpty(row7))
continue;
boolean isMerge7 = HelpUtils.isMergedRegion(sheet, 6, 1);
Element description = set.addElement("description");
if (isMerge7) {
// 在set结点下添加相应的contentDescription子结点
description.setText(HelpUtils.getMergedRegionValue(sheet, 6, 1));
}
// 创建第二级节点 trackList
Element trackList = null;
// 创建trackList节点 下的track节点
Element track = null;
// 在audioInfo结点下添加相应的trackList子结点
trackList = audioInfo.addElement("trackList");
// 获取声道说明列表
for (int j = 8; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
if (HelpUtils.ifRowNullOrEmpty(row))
continue;
// 在trackList结点下添加相应的track子结点
track = trackList.addElement("track");
// 在track结点下添加相应的trackNo子结点
Element trackNo = track.addElement("trackNo");
trackNo.setText(HelpUtils.getCellValue(row.getCell(0)));
// 在track结点下添加相应的fileName子结点
Element filename = track.addElement("fileName");
filename.setText(HelpUtils.getCellValue(row.getCell(1)));
// 在track结点下添加相应的content子结点
Element content = track.addElement("content");
content.setText(HelpUtils.getCellValue(row.getCell(2)));
// 在track结点下添加相应的trackInfo子结点
Element trackInfo = track.addElement("trackInfo");
trackInfo.setText(HelpUtils.getCellValue(row.getCell(3)));
// 在track结点下添加相应的device子结点
Element device = track.addElement("device");
device.setText(HelpUtils.getCellValue(row.getCell(4)));
// 在track结点下添加相应的soundInfo子结点
Element soundInfo = track.addElement("soundInfo");
soundInfo.setText(HelpUtils.getCellValue(row.getCell(5)));
// 在track结点下添加相应的remark子结点
Element remark = track.addElement("remark");
remark.setText(HelpUtils.getCellValue(row.getCell(6)));
}
}
}
} else if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLSX)) {
// 是excel2007以后
book = tableObject.getXssfBook();
for (int i = 0; i < tableObject.getSheet_number(); i++) {
FileInputStream inp = new FileInputStream(fileName);
XSSFWorkbook wb = new XSSFWorkbook(inp);
Sheet sheet = wb.getSheetAt(i);
// 判断该工作簿是否为空
if (HelpUtils.ifSheetNullOrEmpty(sheet))
continue;
// 获取excel标题 标题为第一行合并的内容
//String excelTitle = HelpUtils.getMergedRegionValue(sheet, 0, 0);
Row row2 = sheet.getRow(1); // 获得工作薄的第二行
if (HelpUtils.ifRowNullOrEmpty(row2))
continue;
//判断sheet的格式是否一致,是否为需要导成xml的sheet
if("类型名称".equals(HelpUtils.getCellValue(row2.getCell(0)))){
// 创建第一级结点,audioInfo
Element audioInfo = null;
// 创建第二级节点 set
Element set = null;
// 在根目录下添加一个audioInfo的子结点
audioInfo = root.addElement("audioInfo");
// 在MultipleAudioInfo结点下添加相应的set子结点
set = audioInfo.addElement("set");
// 在set结点下添加相应的name子结点
Element name = set.addElement("name");
name.setText(HelpUtils.getCellValue(row2.getCell(1)));
// 在set结点下添加相应的type子结点
Element type = set.addElement("type");
type.setText(HelpUtils.getCellValue(row2.getCell(4)));
// 在set结点下添加相应的director子结点
Element director = set.addElement("director");
director.setText(HelpUtils.getCellValue(row2.getCell(6)));
Row row3 = sheet.getRow(2); // 获得工作薄的第三行
if (HelpUtils.ifRowNullOrEmpty(row3))
continue;
// 在set结点下添加相应的orderName子结点
Element orderName = set.addElement("orderName");
orderName.setText(HelpUtils.getCellValue(row3.getCell(1)));
// 在set结点下添加相应的orderNo子结点
Element orderNo = set.addElement("orderNo");
orderNo.setText(HelpUtils.getCellValue(row3.getCell(4)));
// 在set结点下添加相应的orderPhone子结点
Element orderPhone = set.addElement("orderPhone");
orderPhone.setText(HelpUtils.getCellValue(row3.getCell(6)));
Row row4 = sheet.getRow(3); // 获得工作薄的第四行
if (HelpUtils.ifRowNullOrEmpty(row4))
continue;
// 在set结点下添加相应的orderTime子结点
Element orderTime = set.addElement("orderTime");
orderTime.setText(HelpUtils.getCellValue(row4.getCell(1)));
// 在set结点下添加相应的orderAddress子结点
Element orderAddress = set.addElement("orderAddress");
orderAddress.setText(HelpUtils.getCellValue(row4.getCell(4)));
// 在set结点下添加相应的orderStandard子结点
Element orderStandard = set.addElement("orderStandard");
orderStandard.setText(HelpUtils.getCellValue(row4.getCell(6)));
Row row5 = sheet.getRow(4); // 获得工作薄的第五行
if (HelpUtils.ifRowNullOrEmpty(row5))
continue;
// 在set结点下添加相应的setName子结点
Element setName = set.addElement("setName");
setName.setText(HelpUtils.parseDate(row5.getCell(1)));
// 在set结点下添加相应的setType子结点
Element setType = set.addElement("setType");
setType.setText(HelpUtils.getCellValue(row5.getCell(4)));
// 在set结点下添加相应的recorderPhone子结点
Element setNo = set.addElement("setNo");
setNo.setText(HelpUtils.getCellValue(row5.getCell(6)));
Row row6 = sheet.getRow(5); // 获得工作薄的第六行
if (HelpUtils.ifRowNullOrEmpty(row6))
continue;
boolean isMerge6 = HelpUtils.isMergedRegion(sheet, 5, 1);
// 在set结点下添加相应的trackCount子结点
Element trackCount = set.addElement("trackCount");
if (isMerge6) {
trackCount.setText(HelpUtils.getMergedRegionValue(sheet, 5, 1));
}
Row row7 = sheet.getRow(6); // 获得工作薄的第七行
if (HelpUtils.ifRowNullOrEmpty(row7))
continue;
boolean isMerge7 = HelpUtils.isMergedRegion(sheet, 6, 1);
// 在set结点下添加相应的description子结点
Element description = set.addElement("description");
if (isMerge7) {
description.setText(HelpUtils.getMergedRegionValue(sheet, 6, 1));
}
// 创建第二级节点 trackList
Element trackList = null;
// 创建trackList节点 下的track节点
Element track = null;
// 在audioInfo结点下添加相应的trackList子结点
trackList = audioInfo.addElement("arackList");
// 获取声道说明列表
for (int j = 8; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
if (HelpUtils.ifRowNullOrEmpty(row))
continue;
// 在trackList结点下添加相应的track子结点
track = trackList.addElement("track");
// 在track结点下添加相应的trackNo子结点
Element trackNo = track.addElement("trackNo");
trackNo.setText(HelpUtils.getCellValue(row.getCell(0)));
// 在audioTrack结点下添加相应的fileName子结点
Element filename = track.addElement("fileName");
filename.setText(HelpUtils.getCellValue(row.getCell(1)));
// 在track结点下添加相应的content子结点
Element content = track.addElement("content");
content.setText(HelpUtils.getCellValue(row.getCell(2)));
// 在track结点下添加相应的trackInfo子结点
Element trackInfo = track.addElement("trackInfo");
trackInfo.setText(HelpUtils.getCellValue(row.getCell(3)));
// 在track结点下添加相应的device子结点
Element device = track.addElement("device");
device.setText(HelpUtils.getCellValue(row.getCell(4)));
// 在track结点下添加相应的soundInfo子结点
Element soundInfo = track.addElement("soundInfo");
soundInfo.setText(HelpUtils.getCellValue(row.getCell(5)));
// 在track结点下添加相应的remark子结点
Element remark = track.addElement("remark");
remark.setText(HelpUtils.getCellValue(row.getCell(6)));
}
}
}
} else {
HelpUtils.print("nonsupport file format");
throw new RuntimeException("nonsupport file format, please check input fileName again");
}
// String outFile = "C:\\Users\\wxs\\Desktop\\excel转xml\\wdx.xml";
String outFile = storagePath;
// 格式化XML
OutputFormat format = OutputFormat.createPrettyPrint();
// 是否缩进
format.setIndent(true);
// 在声明完之后是否进行换行
format.setNewLineAfterDeclaration(false);
// 设置字符编码,只是在声明里把encoding的值设置为UTF-8
format.setEncoding("UTF-8");
//format.setEncoding("gb2312");
// 进否格式化
format.setTrimText(true);
// 进否添加新行
format.setNewlines(true);
//XMLWriter writer = new XMLWriter(new FileWriter(outFile), format); //此处不能这么写,否则在浏览器中打开会出现中文乱码的问题
BufferedWriter fos = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile,true), "utf-8")); //正确写法
//FileOutputStream fos = new FileOutputStream(outFile);
XMLWriter writer = new XMLWriter(fos, format);
writer.write(docu);
// 写完之后要进行flush这样才会把数据真正的写到文件中去
writer.flush();
writer.close();
System.out.println("===成功生成XML文件!!!===");
}
/**
* extract method object
*/
private static class TableObject {
private String fileName;
private XSSFWorkbook xssfBook;
private HSSFWorkbook hssfBook;
private int sheet_number;
public TableObject(String fileName) {
this.fileName = fileName;
}
public XSSFWorkbook getXssfBook() {
return xssfBook;
}
public HSSFWorkbook getHssfBook() {
return hssfBook;
}
public int getSheet_number() {
return sheet_number;
}
/**
*
* @return
*/
public TableObject invoke() {
xssfBook = null;
hssfBook = null;
sheet_number = 0;
try {
FileInputStream is = new FileInputStream(new File(fileName));
if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLS)) {
hssfBook = new HSSFWorkbook(is);
sheet_number = hssfBook.getNumberOfSheets();
} else if (HelpUtils.validExcelFormat(fileName, SUFFIX_XLSX)) {
xssfBook = new XSSFWorkbook(is);
sheet_number = xssfBook.getNumberOfSheets();
}
} catch (IOException e) {
e.printStackTrace();
}
return this;
}
}
}
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
public class HelpUtils {
public static final String NO_FILE_NULL = "NO FILE NULL";
public static final String NO_FILE_EMPTY = "NO FILE EMPTY";
// 这里可以提供更多地编码格式,另外由于部分编码格式是一致的所以会返回 第一个匹配的编码格式 GBK 和 GB2312
public static final String[] encodes = new String[] { "UTF-8", "GBK", "GB2312", "ISO-8859-1", "ISO-8859-2" };
/**
* 判断该工作薄是否为空
* @param sheet
* @return
*/
public static boolean ifSheetNullOrEmpty(Sheet sheet) {
if (sheet == null || sheet.getLastRowNum() == 0) {
return true;
}
return false;
}
/**
* 判断该行是否为空
* @param row
* @return
*/
public static boolean ifRowNullOrEmpty(Row row) {
if (row == null || row.getLastCellNum() == 0 || row.getCell(0) == null) {
return true;
}
return false;
}
/**
* ifXls:判断是否为 xls 文件
*
* @param fileName 包含后缀的文件名
*/
public static boolean validExcelFormat(String fileName,String type) {
if(getOS().contains("win")){
return valid(fileName) && getFileNameSuffix(fileName).equalsIgnoreCase(type);
}else if(getOS().contains("linux")){
return valid(fileName) && getFileNameSuffix(fileName).equals(type);
}else{
print("System OS is not windows or linux");
throw new RuntimeException("System OS is not windows or linux , if you use this class in another sys,please implement yourself");
}
}
/**
* valid :判断文件是否存在
*
* @param fileName fileName 文件名。
* 区分为文件名为null,或者为空。
*/
public static boolean valid(String fileName) {
return !ifNull(fileName) && !ifEmpty(fileName);
}
/**
* 判断文件名字是否为empty
* @param fileName
* @return
*/
public static boolean ifEmpty(String fileName) {
if (fileName.equals("")) {
print(NO_FILE_EMPTY);
return true;
}
return false;
}
/**
* 判断文件名字是否为null
* @param fileName
* @return
*/
public static boolean ifNull(String fileName) {
if (fileName == null) {
print(NO_FILE_NULL);
return true;
}
return false;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
*
* @return
*/
public static String getOS() {
return System.getProperty("os.name").toLowerCase();
}
/**
* 打印
* @param msg
*/
public static void print(String msg) {
System.out.println(msg);
}
/**
* 获取文件名称的后缀
* @param fileName
* @return
*/
public static String getFileNameSuffix(String fileName) {
return fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
//判断单元格数据的类型,不同类型调用不同的方法
switch (cell.getCellType()) {
//数值类型
case Cell.CELL_TYPE_NUMERIC:
//进一步判断 ,单元格格式是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
//数值
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//判断单元格是公式格式,需要做一种特殊处理来得到相应的值
case Cell.CELL_TYPE_FORMULA:{
try{
cellValue = String.valueOf(cell.getNumericCellValue());
}catch(IllegalStateException e){
cellValue = String.valueOf(cell.getRichStringCellValue());
}
}
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
/**
* 判断单元格格式,转化日期格式,日期在poi里保存的是数字,所以这里要转化一下.
* @param cell 单元格
* @return
*/
public static String parseDate(Cell cell) {
String result = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:// 数字类型
if (DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("yyyy/MM/dd")) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} 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 {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
break;
case Cell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_BLANK:
result = "";
break;
default:
result = "";
break;
}
return result;
}
/**
* 获取合并单元格的值
*
* @param sheet 所在的页薄数
* @param row 起始的行数下标
* @param column 起始的列数下标
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return HelpUtils.getCellValue(fCell);
}
}
}
return null;
}
/**
* 获取字符串编码格式
*
* @param str
* @return
*/
public static String getEncode(String str) {
byte[] data = str.getBytes();
byte[] b = null;
a:for (int i = 0; i < encodes.length; i++) {
try {
b = str.getBytes(encodes[i]);
if (b.length!=data.length)
continue;
for (int j = 0; j < b.length; j++) {
if (b[j] != data[j]) {
continue a;
}
}
return encodes[i];
} catch (UnsupportedEncodingException e) {
continue;
}
}
return null;
}
}
五、Run as 运行
六、在对应的路径找到相应的xml,用浏览器打开