ExcelHelper.cs
引用命名空间:
using System.Threading;
using System.Reflection;
using System.Globalization;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
/// <summary>
/// 导出Excel/// </summary>
public class ExportExcel
{
/// <summary>
/// 导出DataTable到Excel
/// </summary>
/// <param name="dt">DataTable数据</param>
public static void ExportDataTableToExcel(System.Data.DataTable dt)
{
Application appexcel = new Application();
Missing miss = Missing.Value;
Workbook workbookdata;
Worksheet worksheetdata;
Range rangedata;
//设置对象不可见
appexcel.Visible = false;
CultureInfo currentci = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头,所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数,最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值,可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数,最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
icurrsize = irowcount - iparstedrow;
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性,把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
appexcel.Visible = true;
appexcel.SaveWorkspace("A");
Helper.KillExcelProcess(appexcel);
}
}
/// <summary>
/// 导入Excel
/// </summary>
public class ImportExcel
{
/// <summary>
/// 从Excel导入数据到DataTable
/// </summary>
/// <param name="excelFileName">Excel文件名</param>
/// <returns></returns>
public static System.Data.DataTable ImportUnknowExcel(string excelFileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Application();
wbs = app.Workbooks;
wbs.Add(excelFileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
string name = ws.Name;
//行数
int rows = 100000000;
//列数
int columns = 10000;
string columnName = string.Empty;
for (int i = 1; i < columns; i++)
{
Range range = ws.get_Range(app.Cells[1, i], app.Cells[1, i]);
range.Select();
columnName = app.ActiveCell.Text.ToString();
if (!string.IsNullOrEmpty(columnName))
{
System.Data.DataColumn newColumn = new System.Data.DataColumn(columnName);
dt.Columns.Add(newColumn);
}
else
{
columns = i - 1;
break;
}
}
bool bIsEnd = false;
string readContent = string.Empty;
for (int i = 2; i < rows; i++)
{
System.Data.DataRow dr = dt.NewRow();
for (int j = 1; j <= columns; j++)
{
Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
range.Select();
readContent = app.ActiveCell.Text.ToString();
if (string.IsNullOrEmpty(readContent))
bIsEnd = true;
else
{
dr[j - 1] = readContent;
bIsEnd = false;
}
}
if (bIsEnd)
break;
else
dt.Rows.Add(dr);
}
Helper.KillExcelProcess(app);
}
catch (Exception ex)
{
throw new Exception(string.Format("导入Excel失败,原因:{0}", ex.Message));
}
return dt;
}
}
public class Helper
{
#region 杀死Excel进程
/// <summary>
/// 调用系统api获得进程唯一标识
/// </summary>
/// <param name="hwnd">句柄</param>
/// <param name="ID">返回进程ID</param>
/// <returns></returns>
[DllImport("User32.dll", CharSet = CharSet.Auto)]
private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 杀死Excel进程
/// </summary>
/// <param name="excel">Excel进程</param>
public static void KillExcelProcess(Application excel)
{
// 得到这个句柄,具体作用是得到这块内存入口
IntPtr t = new IntPtr(excel.Hwnd);
// 得到本进程唯一标志k
int k = 0;
GetWindowThreadProcessId(t, out k);
// 得到对进程k的引用
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
//关闭进程k
p.Kill();
}
#endregion
}