第一种方式 :需引用 Microsoft.Office.Interop.Excel,安装office
public bool ExportToExcel()
{
Microsoft.Office.Interop.Excel.Application xlApp;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception ex)
{
xlApp = null;
GC.Collect();//强行销毁
return false
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写Title
var titleRowCount = 0;
if (titleRowCount != 0)
MergeCells(worksheet, 1, 1, titleRowCount, 8, "销售代开发票");
//写入列标题
worksheet.Cells[titleRowCount + 1, 1] = "序号";
worksheet.Cells[titleRowCount + 1, 2] = "发货时间";
worksheet.Cells[titleRowCount + 1, 3] = "销售订单号";
worksheet.Cells[titleRowCount + 1, 4] = "CAS";
worksheet.Cells[titleRowCount + 1, 5] = "产品名称";
worksheet.Cells[titleRowCount + 1, 6] = "包装";
worksheet.Cells[titleRowCount + 1, 7] = "价格";
worksheet.Cells[titleRowCount + 1, 8] = "开票产品名称";
//写入数值
for (int r = 0; r <= data.Count - 1; r++)
{
worksheet.Cells[r + titleRowCount + 2, 1] = r + 1;
worksheet.Cells[r + titleRowCount + 2, 2] = data[r].SendGoodDate;
worksheet.Cells[r + titleRowCount + 2, 3] = data[r].SaleOderNumber;
worksheet.Cells[r + titleRowCount + 2, 4] = data[r].Cas;
worksheet.Cells[r + titleRowCount + 2, 5] = data[r].ProductName;
worksheet.Cells[r + titleRowCount + 2, 6] = data[r].PackAge;
worksheet.Cells[r + titleRowCount + 2, 7] = data[r].Price;
worksheet.Cells[r + titleRowCount + 2, 8] = data[r].ProductNameOfPurchaseInvoice;
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
bool fileSaved = true;
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(Server.MapPath(saveFileName));
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
if (workbook != null)
{
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
workbooks = null;
}
if (xlApp != null)
{
xlApp.Quit();
if (xlApp != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
xlApp = null;
}
GC.Collect();//强行销毁
}
}
if (workbook != null)
{
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
workbooks = null;
}
if (xlApp != null)
{
xlApp.Quit();
}
GC.Collect();//强行销毁
if (xlApp != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
xlApp = null;
if (!fileSaved)
{
return false;
}
return true;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range((object)workSheet.Cells[beginRowIndex, beginColumnIndex], (object)workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
第二种方式:服务器端无需安装office
1.在服务器放置一空excel文件
2.copy一份新excel
3.导出数据到该excel
public static string GetSaleOrderWaitPendingInvoiceExportExcelPath(DataTable dt, string templateFileName, string exportFileName)
{
if (string.IsNullOrEmpty(templateFileName) || string.IsNullOrEmpty(templateFileName))
{
return "路径空";
}
File.Copy(templateFileName, exportFileName);
OleDbConnection OleDb_Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + exportFileName + ";" + "Extended Properties="Excel 12.0 Xml;HDR=Yes;IMEX=0;"");
try
{
OleDb_Conn.Open();
OleDbCommand OleDb_Comm = new OleDbCommand();
OleDb_Comm.Connection = OleDb_Conn;
string strCmd;
try
{
strCmd = "drop table [Sheet1$]";
OleDb_Comm.CommandText = strCmd;
OleDb_Comm.ExecuteNonQuery();
}
catch
{
}
strCmd = "create Table [Sheet1$](";
strCmd += "[序号] TEXT,"; //此处 TEXT 是为了 防止导出数据的长度大于255而导出异常
strCmd += "[发货时间] TEXT,";
strCmd += "[销售订单号] TEXT,";
strCmd += "[CAS] TEXT,";
strCmd += "[产品名称] TEXT,";
strCmd += "[包装] TEXT,";
strCmd += "[价格] TEXT,";
strCmd += "[开票产品名称] TEXT,";
strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1);
strCmd += ")";
OleDb_Comm.CommandText = strCmd;
OleDb_Comm.ExecuteNonQuery();
StringBuilder CommText = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
CommText.Append("INSERT INTO [Sheet1$] VALUES(");
CommText.Append("'" + dr[0].ToString().Replace("'","''") + "',");
CommText.Append("'" + dr[1].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[2].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[3].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[4].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[5].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[6].ToString().Replace("'", "''") + "',");
CommText.Append("'" + dr[7].ToString().Replace("'", "''") + "'");
CommText.Append(");");
//导出数据
OleDb_Comm.CommandText = CommText.ToString().Replace(':', ';').TrimEnd(';');
OleDb_Comm.ExecuteNonQuery();
CommText.Remove(0, CommText.Length);
}
}
catch
{
OleDb_Conn.Close();
return "导出数据时报错";
}
OleDb_Conn.Close();
return "";
}