//获取用户打开的Excel文档路径
private stringkkk()
{
OpenFileDialog selectFile = new OpenFileDialog();
selectFile.Multiselect = false;
selectFile.Filter = "Excel Files(*.xls,*.xlsx)|*.xls;*.xlsx";
if (selectFile.ShowDialog() != DialogResult.OK)
return null;
string filePath = selectFile.FileName;
return filePath ;
}
/// <summary>
/// 创建Excel Table.
/// </summary>
/// <param name="colCount">列数</param>
/// <returns>DataTable</returns>
private System.Data.DataTable CreateExcelTable(int colCount)
{
System.Data.DataTable returnTable = new System.Data.DataTable();
for (int i = ; i <= colCount; i++)
returnTable.Columns.Add("col" + i.ToString(), typeof(string));
return returnTable;
} /// <summary>
/// 根据Excel路径,读取数据至DataSet.
/// </summary>
/// <param name="excelPath">Excel Path</param>
/// <returns>DataSet</returns>
public DataSet GetDataSetFromExcel(string excelPath)
{
DataSet resultDS = new DataSet();
Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook();
excelBook.Open(excelPath); // get the rows and insert into dataset.
Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[];
if (!excelSheet.IsVisible)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏的Sheet:[{0}],请检查!", excelSheet.Name), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
} Aspose.Cells.Cells excelValues = excelSheet.Cells;
foreach (Row r in excelValues.Rows)
{
if (r.IsHidden)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏行,行号:[{0}],请检查!", r.Index + ), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
}
}
foreach (Column c in excelValues.Columns)
{
if (c.IsHidden)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏列,列号:[{0}],请检查!", (char)(c.Index + )), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
}
}
int rowCount = excelValues.MaxRow;
int colCount = excelValues.MaxColumn; System.Data.DataTable excelTable = CreateExcelTable(colCount);
resultDS.Tables.Add(excelTable);
for (int i = ; i <= rowCount; i++)
{
//如果前5栏为空的话,则忽略添加新行。
if (Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == "")
continue;
DataRow row = excelTable.NewRow();
for (int j = ; j <= colCount; j++)
{
if (excelValues[i, j].Value == null)
row[j] = "";
else
row[j] = excelValues[i, j].Value.ToString();
}
excelTable.Rows.Add(row);
}
return resultDS;
} /// <summary>
/// 根据Excel路径,读取指定Sheet表数据至DataSet.
/// </summary>
/// <param name="excelPath">Excel Path</param>
/// <returns>DataSet</returns>
public DataSet GetDataSetFromExcel_SG3Nod(string excelPath,int x)
{
DataSet resultDS = new DataSet();
Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook();
excelBook.Open(excelPath); // get the rows and insert into dataset.
Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[x];
if (!excelSheet.IsVisible)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏的Sheet:[{0}],请检查!", excelSheet.Name), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
} Aspose.Cells.Cells excelValues = excelSheet.Cells;
foreach (Row r in excelValues.Rows)
{
if (r.IsHidden)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏行,行号:[{0}],请检查!", r.Index + ), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
}
}
foreach (Column c in excelValues.Columns)
{
if (c.IsHidden)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("Excel文件发现隐藏列,列号:[{0}],请检查!", (char)(c.Index + )), "P2解决方案", MessageBoxButtons.OK, MessageBoxIcon.Error);
return resultDS;
}
}
int rowCount = excelValues.MaxRow;
int colCount = excelValues.MaxColumn; System.Data.DataTable excelTable = CreateExcelTable(colCount);
resultDS.Tables.Add(excelTable);
for (int i = ; i <= rowCount; i++)
{
//如果前5栏为空的话,则忽略添加新行。
if (Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == ""
&& Convert.ToString(excelValues[i, ].Value) == "")
continue;
DataRow row = excelTable.NewRow();
for (int j = ; j <= colCount; j++)
{
if (excelValues[i, j].Value == null)
row[j] = "";
else
row[j] = excelValues[i, j].Value.ToString();
}
excelTable.Rows.Add(row);
}
return resultDS;
} /// <summary>
/// 读取Excel第一个Sheet至DataTable
/// </summary>
/// <param name="excelPath"></param>
/// <returns></returns>
public DataTable GetDataTableFromExcel(string excelPath)
{
DataSet resultDS = new DataSet();
Aspose.Cells.Workbook excelBook = new Aspose.Cells.Workbook(excelPath);
//excelBook.Open(excelPath); // get the rows and insert into dataset.
Aspose.Cells.Worksheet excelSheet = excelBook.Worksheets[];
Aspose.Cells.Cells excelValues = excelSheet.Cells;
int rowCount = excelValues.MaxRow + ;
int colCount = excelValues.MaxColumn + ;
return excelValues.ExportDataTable(, , rowCount, colCount); }