这种方式需要先引用 Microsoft.Office.Interop.Excel 。可以非常灵活的读取Excel中的数据，而且使用方式很丰富，基本上凡是打开Office Excel软件能够用鼠标点击完成的事，使用VSTO调用COM组件都能完成，而且可以调用Excel自身带的宏方法等。
但是，如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。最重要的一点因为是基于单元格方式读取的，所以速度很慢。个人建议是，如果在大数据的循环中，比如for(int i=0; i<10000;i++){……}在这里的逻辑中不要使用COM对象，因为毕竟COM对象是非托管代码，调用时会有一个托管对象到非托管对象的转换，这好比是要经过的一扇门，比较费时费力的。
一、读取Excel
Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
这里会出现一个错误“object”未包含“get_Range”的定义。
改成 Microsoft.Office.Interop.Excel.Range range = ws.Range[app.Cells[i, j], app.Cells[i, j]]; 就可以了
下面附上单线程和多线程两种方式：
单线程：
/// <summary>
/// COM组件方式解析Excel，返回DataTable
/// </summary>
/// <param name="fileName">Excel路径名</param>
/// <returns></returns>
public static System.Data.DataTable COMImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Workbooks wbs;
Microsoft.Office.Interop.Excel.Worksheet ws;
object oMissiong = System.Reflection.Missing.Value;
app = new Microsoft.Office.Interop.Excel.Application(); //lauch excel application
wbs = app.Workbooks;
wbs.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
ws = (Microsoft.Office.Interop.Excel.Worksheet)app.Worksheets.get_Item(1); //取得第一个工作薄
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
dt.TableName = ws.Name;
for (int i = 1; i <=rows; i++)
{
System.Data.DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
Microsoft.Office.Interop.Excel.Range range = ws.Range[app.Cells[i, j], app.Cells[i, j]];
range.Select();
if (i == 1) //读取列头
{
string colName = app.ActiveCell.Text.ToString();
if (dt.Columns.Contains(colName)) //是否存在重复列名
{
dt.Columns.Add(colName + j);
}
else { dt.Columns.Add(colName); }
}
dr[j - 1] = app.ActiveCell.Text.ToString();
}
dt.Rows.Add(dr);
}
app.Quit(); app = null;
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("excel");
foreach (System.Diagnostics.Process pro in procs)
{
pro.Kill(); //没有更好的方法,只有杀掉进程
}
GC.Collect();
dt.Rows.RemoveAt(0); //上面那样写把列名也读进去了，在这里移除一下。也可以在上面把读列名单独出来
return dt;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}多线程：
/// <summary>
/// 使用COM，多线程读取Excel（1 主线程、4 副线程）
/// </summary>
/// <param name="excelFilePath">Excel路径</param>
/// <returns>DataTabel</returns>
public static System.Data.DataTable ThreadReadExcel(string excelFilePath)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //lauch excel application
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
if (app == null)
{
return null;
}
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
//将数据读入到DataTable中——Start
sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null)
return null;
string cellContent;
dt.TableName = worksheet.Name;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Microsoft.Office.Interop.Excel.Range range;
//负责列头Start
System.Data.DataColumn dc;
int ColumnID = 1;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
while (iColCount >= ColumnID)
{
dc = new System.Data.DataColumn();
dc.DataType = System.Type.GetType("System.String");
string strNewColumnName = range.Text.ToString().Trim();
if (strNewColumnName.Length == 0) strNewColumnName = "_1";
//判断列名是否重复
for (int i = 1; i < ColumnID; i++)
{
if (dt.Columns[i - 1].ColumnName == strNewColumnName)
strNewColumnName = strNewColumnName + "_1";
}
dc.ColumnName = strNewColumnName;
dt.Columns.Add(dc);
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID];
}
//End
//数据大于500条，使用多进程进行读取数据
if (iRowCount - 1 > 500)
{
//开始多线程读取数据
//新建线程
int b2 = (iRowCount - 1) / 10;
System.Data.DataTable dt1 = new System.Data.DataTable("dt1");
dt1 = dt.Clone();
SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);
System.Threading.Thread othread1 = new System.Threading.Thread(new System.Threading.ThreadStart(sheet1thread.SheetToDataTable));
othread1.Start(); //启动线程
//阻塞 1 毫秒，保证第一个读取 dt1
System.Threading.Thread.Sleep(1);
System.Data.DataTable dt2 = new System.Data.DataTable("dt2");
dt2 = dt.Clone();
SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);
System.Threading.Thread othread2 = new System.Threading.Thread(new System.Threading.ThreadStart(sheet2thread.SheetToDataTable));
othread2.Start();
System.Data.DataTable dt3 = new System.Data.DataTable("dt3");
dt3 = dt.Clone();
SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);
System.Threading.Thread othread3 = new System.Threading.Thread(new System.Threading.ThreadStart(sheet3thread.SheetToDataTable));
othread3.Start();
System.Data.DataTable dt4 = new System.Data.DataTable("dt4");
dt4 = dt.Clone();
SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);
System.Threading.Thread othread4 = new System.Threading.Thread(new System.Threading.ThreadStart(sheet4thread.SheetToDataTable));
othread4.Start();
//主线程读取剩余数据
for (int iRow = b2 * 4 + 2; iRow <= iRowCount; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
othread1.Join();
othread2.Join();
othread3.Join();
othread4.Join();
//将多个线程读取出来的数据追加至 dt1 后面
foreach (System.Data.DataRow dr in dt2.Rows)
dt1.Rows.Add(dr.ItemArray);
dt2.Clear();
dt2.Dispose();
foreach (System.Data.DataRow dr in dt3.Rows)
dt1.Rows.Add(dr.ItemArray);
dt3.Clear();
dt3.Dispose();
foreach (System.Data.DataRow dr in dt4.Rows)
dt1.Rows.Add(dr.ItemArray);
dt4.Clear();
dt4.Dispose();
foreach (System.Data.DataRow dr in dt.Rows)
dt1.Rows.Add(dr.ItemArray);
dt.Clear();
dt.Dispose();
return dt1;
}
else
{
for (int iRow = 2; iRow <= iRowCount; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
}
//将数据读入到DataTable中——End
return dt;
}
catch
{
return null;
}
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}SheetOptions类：
class SheetOptions
{
Microsoft.Office.Interop.Excel.Worksheet worksheet;
int iColCount;
int star;
int end;
System.Data.DataTable dt;
public SheetOptions(Microsoft.Office.Interop.Excel.Worksheet worksheet, int iColCount,int star,int end,System.Data.DataTable dt)
{
this.worksheet = worksheet;
this.iColCount = iColCount;
this.star = star;
this.end = end;
this.dt = dt;
}
public void SheetToDataTable()
{
string cellContent;
Microsoft.Office.Interop.Excel.Range range;
for (int iRow = star; iRow <= end; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
dr[iCol - 1] = cellContent;
}
dt.Rows.Add(dr);
}
}
}拿1000*23的Excel测试了一下,多线程耗时1分40秒，单线程耗时10分50秒，再多的不想测了。
二、写入Excel
虽然COM组件方式可以灵活的操作Excel,设置Excel单元格样式等，但是在写入数据时一行一行的调用COM的Range row对象去赋值，是相当慢。下面给出两种写入Excel方式，分别是按单元格写入，和按范围写入。
按单元格写入：
/// <summary>
/// Dataset导出为EXCEL 按单元格写入
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="fileName">路径</param>
public static void ToExcelSheet(System.Data.DataSet ds, string fileName)
{
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application(); //建立一个Excel进程
Microsoft.Office.Interop.Excel.Workbook workbookData = null;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
Microsoft.Office.Interop.Excel.Range range;
try
{
workbookData = appExcel.Workbooks.Add(System.Reflection.Missing.Value); //生成新Workbook
appExcel.DisplayAlerts = false;//不显示警告
for (int k = 0; k < ds.Tables.Count; k++)
{
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
if (ds.Tables[k] != null)
{
worksheetData.Name = ds.Tables[k].TableName;
//写入标题
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[1, i + 1] = ds.Tables[k].Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheetData.Cells[1, i + 1];
range.RowHeight = 25; //行高
//range.EntireRow.AutoFit(); //自动调整行高
range.Interior.ColorIndex = 15; //设置颜色
range.Font.Bold = true;
range.NumberFormatLocal = "@";//文本格式
range.EntireColumn.AutoFit();//自动调整列宽
// range.WrapText = true; //文本自动换行
}
//写入数值
for (int r = 0; r < ds.Tables[k].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[r + 2, i + 1] = ds.Tables[k].Rows[r][i];
//Range myrange = worksheetData.get_Range(worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]);
//myrange.NumberFormatLocal = "@";//文本格式
}
}
}
worksheetData.Columns.EntireColumn.AutoFit();
workbookData.Saved = true;
}
}
catch { }
finally
{
//workbookData.SaveAs(fileName, System.Reflection.Missing.Value, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //保存成Excel文件
workbookData.SaveCopyAs(fileName);
workbookData.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
appExcel.Quit();
GC.Collect();
}
}按范围写入：
/// <summary>
/// DataTable导出为EXCEL （按范围写入）
/// </summary>
/// <param name="ds">DataTable</param>
/// <param name="fileName">路径</param>
public static void ToExcelSheetTWO(System.Data.DataTable dt, string fileName)
{
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application(); //建立一个Excel进程
Microsoft.Office.Interop.Excel.Workbook workbookData = null;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
Microsoft.Office.Interop.Excel.Range range;
try
{
workbookData = appExcel.Workbooks.Add(System.Reflection.Missing.Value); //生成新Workbook
appExcel.DisplayAlerts = false;//不显示警告
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
if (dt != null)
{
worksheetData.Name = dt.TableName;
int rowNumber = dt.Rows.Count;//不包括字段名
int columnNumber = dt.Columns.Count;
int colIndex = 0;
//写入标题
foreach (System.Data.DataColumn col in dt.Columns)
{
colIndex++;
//appExcel.Cells[1, colIndex] = col.ColumnName;
worksheetData.Cells[1, colIndex] = col.ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheetData.Cells[1, colIndex];
range.RowHeight = 25; //行高
//range.EntireRow.AutoFit(); //自动调整行高
range.Interior.ColorIndex = 15; //设置颜色
//range.Font.Size = 10; //字体大小
range.Font.Bold = true; //加粗
range.NumberFormatLocal = "@";//文本格式
range.EntireColumn.AutoFit();//自动调整列宽
// range.WrapText = true; //文本自动换行
//range.ColumnWidth = 25; //列宽
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
}
object[,] objData = new object[rowNumber, columnNumber];
//范围
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dt.Rows[r][c];
}
}
// 写入Excel
range = worksheetData.Range[appExcel.Cells[2, 1], appExcel.Cells[rowNumber + 1, columnNumber]];
range.Value2 = objData;
//worksheetData.get_Range(appExcel.Cells[2, 1], appExcel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
}
worksheetData.Columns.EntireColumn.AutoFit();
workbookData.Saved = true;
}
catch { }
finally
{
//workbookData.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbookData.SaveCopyAs(fileName);
workbookData.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
appExcel.Quit();
GC.Collect();
}
}
4638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
