导入excel（Aspose）

来源：互联网发布：可靠韩国代购淘宝店铺编辑：程序博客网时间：2024/06/08 09:31

//导入excel
private void btnInExcel_Click(object sender, EventArgs e)
{
//ExportToExcel.ReadExcel(strFileName);
OpenFileDialog frm = new OpenFileDialog(); //打开文件
frm.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";
if (frm.ShowDialog() == DialogResult.OK)
{
string excelName = frm.FileName;
//Workbook excel = new Workbook(excelName);
//List<string[]> importyString = GetImportExcelRoute(excel);
//AsposeExcel.ExcelToDatatalbe();
var result= ExportToExcel.ReadExcel(excelName);
int columnCount = result.GetLength(1);
int rowCount = result.GetLength(0);
int start = 1;
if (columnCount == 11)
{
for (int i = 3; i < rowCount; i++)
{
DBHelper.InsertSeries(result[i, 0 + start].ToString(), result[i, 1 + start].ToString(), result[i, 2 + start].ToString(), result[i, 3 + start].ToString(), result[i, 4 + start].ToString(), result[i, 6 + start].ToString(), result[i, 7 + start].ToString() == "" ? 0 : Convert.ToInt32(result[i, 7 + start].ToString()), result[i, 8 + start] == "" ? 0 : Convert.ToInt32(result[i, 8 + start].ToString()), result[i, 9 + start].ToString(), result[i, 5 + start].ToString(), DateTime.Now);
}
}
else
{
MessageBox.Show("导入失败！");
return;
}
}
MessageBox.Show("导入成功！");
frmPMS_Load(sender, e); //刷新gridview

}

//添加数据
public static bool InsertSeries(string SeriesName, string SubSeriesName, string OPIName, string OPICode, string States, string InsideCode, int MinStock, int AddStockNum, string Remarks, string OPI_CODERemarkes, DateTime OPI_Date = default(DateTime))
{
bool result;
string ss = OPIDB.GetConnectionString(DBHelper.ConnConfig);
using (OPIDB sQLNICSDB = new OPIDB(OPIDB.GetConnectionString(DBHelper.ConnConfig)))
{
var user = sQLNICSDB.T_OPI.FirstOrDefault(x => x.OPI_CODE == OPICode && x.SERIES_NAME == SeriesName && x.OPI_NAME == OPIName && x.SUB_NAME == SubSeriesName && x.OPI_INSIDECODE == InsideCode);
if (user == null)
{
result = true;
T_OPI opi = new T_OPI();
if (OPI_Date == default(DateTime))
opi.OPI_DATE = DateTime.Now;
else
opi.OPI_DATE = OPI_Date;
opi.SERIES_NAME = SeriesName;
opi.SUB_NAME = SubSeriesName;
opi.OPI_NAME = OPIName;
opi.OPI_CODE = OPICode;
opi.OPI_STATES = States;
opi.OPI_INSIDECODE = InsideCode;
opi.OPI_MINSTOCK = MinStock;
opi.OPI_ADDSTOCKNUM = AddStockNum;
opi.OPI_REMARKES = Remarks;
opi.OPI_CODERemarkes=OPI_CODERemarkes;

sQLNICSDB.Insert(opi);
}
else
{
result = false;
}
return result;
}
}

//导入excel

public static object[,] ReadExcel(String strFileName)
{
Workbook book = new Workbook(strFileName);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;

var tempCells = cells.ExportArray(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
object[] objTemp = new object[tempCells.GetLength(1)];//输出指定range单元格数据类型至CellValueType二维数组中
for (int i = 1; i <= 4; i++)
{
for (int j = 0; j < tempCells.GetLength(0); j++)
{
if (tempCells[j, i] != null)
objTemp[i] = tempCells[j, i];
else
tempCells[j, i] = objTemp[i];
if (tempCells[j, i] == null)
tempCells[j, i] = "";
}
}
for (int i = 5; i < tempCells.GetLength(1); i++)
{
for (int j = 0; j < tempCells.GetLength(0); j++)
{
if (tempCells[j, i] == null)
tempCells[j, i] = "";
}
}
return tempCells;
}

0 0