package com.robert.Excel;
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;
import java.io.*;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Iterator;
/**
* Created by IntelliJ IDEA.
* User: Administrator
* Date: 11-11-16
* Time: 上午7:14
* To change this template use File | Settings | File Templates.
*/
public class ExcelUtils {
public static final String PATH = "E:\\write.sql";
public static void main(String[] args) {
ExcelUtils excelUtils = new ExcelUtils();
File file = new File("E:\\workbook.xlsx");
XSSFSheet sheet = excelUtils.readExcel(file);
ArrayList<ArrayList> table = excelUtils.resolvingExcelSheet(sheet);
String sql = excelUtils.mergerStr(table,"@@");
excelUtils.write(sql,PATH);
}
/**
* 读取Excel
*/
public XSSFSheet readExcel(File file) {
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
workbook = new XSSFWorkbook(new FileInputStream(file));
sheet = workbook.getSheetAt(0);
} catch (IOException e) {
e.printStackTrace();
}
return sheet;
}
/**
* 将XSSFSheet转化成表格
* @param sheet
* @return
*/
public ArrayList<ArrayList> resolvingExcelSheet(XSSFSheet sheet) {
int numberOfRows = sheet.getPhysicalNumberOfRows();
XSSFRow row = null;
ArrayList<ArrayList> values = new ArrayList<ArrayList>();
ArrayList<String> rowValue = new ArrayList<String>();
if (sheet != null) {
//将sheet分解成行元素
for (int i = 0; i < numberOfRows; i++) {
row = sheet.getRow(i);
//对一行进行再次解析
rowValue = resolvingExcelRow(row);
values.add(rowValue);
}
}
return values;
}
/**
* 将XSSFRow解析成String类型数组
* @param row
* @return
*/
public ArrayList<String> resolvingExcelRow(XSSFRow row) {
int numberOfColumns = row.getPhysicalNumberOfCells();
XSSFCell cell = null;
String cellValue = "";
ArrayList<String> cellValues = new ArrayList<String>();
if (row !=null)
{
for (int i = 0; i < numberOfColumns; i++) {
cell = row.getCell(i);
if(cell!=null)
{
if((cell.getCellType())==XSSFCell.CELL_TYPE_NUMERIC)
{
cellValue = cell.getNumericCellValue()+"";
}
else if(cell.getCellType()==XSSFCell.CELL_TYPE_STRING)
{
cellValue = cell.getStringCellValue();
}
cellValues.add(cellValue);
}
}
}
return cellValues;
}
/**
* 拼接成insert脚本
*/
public String mergerStr(ArrayList<ArrayList> table,String insertStr)
{
String scriptSQL = "";
for(ArrayList arrayList : table)
{
String insertSQL = insertStr;
ArrayList<String> list = (ArrayList<String>)arrayList;
for(int i=0;i<list.size()-1;i++)
{
insertSQL += list.get(i);
insertSQL += ", ";
}
insertSQL += list.get(list.size()-1);
insertSQL += ");";
insertSQL += "\n";
scriptSQL += insertSQL;
}
return scriptSQL;
}
/**
* 写Excel
*/
public void write(String sql,String path) {
PrintWriter printWriter = null;
BufferedWriter bufferedWriter = null;
try {
printWriter = new PrintWriter(path);
bufferedWriter = new BufferedWriter(printWriter);
bufferedWriter.write(sql);
bufferedWriter.flush();
bufferedWriter.close();
printWriter.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try
{
bufferedWriter.close();
printWriter.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
}
使用POI读取Excel并封装成你想要的字符串
最新推荐文章于 2020-08-21 09:33:35 发布