public static System.Data.DataTable ExcelToDatatalbe(int startRow)//导入
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件|*.xls;*.xlsx";
ofd.Title = "选择Excel文件";
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook();
book.Open(ofd.FileName);
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
//获取excel中的数据保存到一个datatable中
System.Data.DataTable dt_Import = cells.ExportDataTableAsString(startRow, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
// dt_Import.
return dt_Import;
}
else
return new System.Data.DataTable();
}
/// <summary>
/// 从Excel获取数据 NPOI
/// </summary>
/// <param name="startRow"></param>
/// <returns></returns>
public static System.Data.DataTable GetExcelData(int startRow, out string msg)
{
#region NPOI方式
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件|*.xls;*.xlsx";
ofd.Title = "选择Excel文件";
ofd.Multiselect = false;
msg = "";
if (ofd.ShowDialog() == DialogResult.OK)
{
bool hasTitle = true;
string fileName = ofd.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Data.DataTable data = new System.Data.DataTable();
try
{
if (app == null) return null;
workbook = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null) return null;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[startRow, i + 1]).Text.ToString();
if (!string.IsNullOrEmpty(txt)) name = txt;
}
while (data.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
data.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = data.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
data.Rows.Add(dr);
}
}
catch (Exception ex)
{
ExceptionUtil.DataCL_HTTP(ex, "", "", FileUtil.GetCurSourceFileName());
msg = ex.Message;
return null;
}
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
return data;
}
#endregion
return null;
}
//选择文件夹
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
if (folderBrowserDialog.ShowDialog(this) == DialogResult.OK)
{
string FolderName = folderBrowserDialog.SelectedPath;
}