using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Net.Mail;
namespace EXCEL操作
{
public class CelValues
{
public string CelValue;
public int CelRow;
public int CelCol;
}
public class EXCELFunction
{
/// <summary>
/// 每页显示单头数
/// </summary>
private int _colsParentRow;
public EXCELFunction (int colsParentRow)
{
_colsParentRow = colsParentRow;
}
/// 取列序号对应的列标识
/// </summary>
/// <param name="aColIndex">列序号,从1开始</param>
/// <returns></returns>
public string GetCelColumnName(int aColIndex)
{
string result = string.Empty;
string temp = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (aColIndex > 0)
{
aColIndex = aColIndex - 1;
int index = aColIndex % 26;
int page = (int)(aColIndex / 26);
if (page == 0)
{
result = Convert.ToString(temp[index]);
}
else
{
result = Convert.ToString(temp[page-1]) + Convert.ToString(temp[index ]);
}
}
return result;
}
/// <summary>
/// 将数据写入EXCEL单元格,形成单据格式
/// </summary>
/// <param name="aBillTitle">单据标题描述</param>
/// <param name="aDtBillHead">单头列表</param>
/// <param name="aDtColsHeader">明细数据列标题</param>
///<param name="aDtBillDetail">单据明细数据</param>
///<param name="aDtBillFoot">单据脚列表</param>
public void WriteCellData(string aBillTitle, System.Data.DataTable aDtBillHead,System.Data.DataTable aDtColsHeader ,System.Data.DataTable aDtBillDetail,System.Data.DataTable aDtBillFoot)
{
int colsParentRow = _colsParentRow;
string billTitle = aBillTitle;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
if (app == null)
{
MessageBox.Show("Excel无法启动");
return;
}
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
//Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Workbook wb=app.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
//写入单头
//dtBillHead 单头内容,行序号
System.Data.DataTable dtBillHead = aDtBillHead;
if ((dtBillHead != null) && (dtBillHead.Rows.Count > 0))
{
for (int k = 0; k < dtBillHead.Rows.Count; k++)
{
int rowIndex = (int)(k / colsParentRow) + 1;
int colIndex = (k % colsParentRow) + 1;
int t = colsParentRow;
if ((dtBillHead.Rows.Count % colsParentRow) != 0)
{
if (rowIndex == Convert.ToInt16(dtBillHead.Rows.Count / colsParentRow) + 1)
{
t = dtBillHead.Rows.Count % colsParentRow;
}
}
rowIndex += billTitle != "" ? 1 : 0;
Microsoft.Office.Interop.Excel.Range rBillHead = ws.get_Range(GetCelColumnName(colIndex) + rowIndex.ToString(), GetCelColumnName(t) + rowIndex.ToString()); //列标题区域
object[] objBillHead = { dtBillHead.Rows[k]["HeadValue"].ToString().Trim() };
rBillHead.Value2 = objBillHead;
rBillHead = rBillHead.get_Resize(1, 1);
rBillHead.EntireColumn.AutoFit();
}
}
IList<string> itemHeader = new List<string>();
for (int i = 0; i < aDtColsHeader.Rows.Count; i++)
{
itemHeader.Add(aDtColsHeader.Rows[i][0].ToString().Trim());
}
int beginRowNo = 1;
if (billTitle != "")
{
beginRowNo = 2;
}
if (dtBillHead != null && dtBillHead.Rows.Count > 0)
{
int k = dtBillHead.Rows.Count / colsParentRow;
k += dtBillHead.Rows.Count % colsParentRow == 0 ? 0 : 1;
beginRowNo += k;
}
Microsoft.Office.Interop.Excel.Range rHeader = ws.get_Range("A" + beginRowNo.ToString(), GetCelColumnName(itemHeader.Count) + beginRowNo.ToString()); //列标题区域
object[] objHeader = new object[itemHeader.Count];
for (int i = 0; i < itemHeader.Count; i++)
{
objHeader[i] = itemHeader[i];
}
rHeader.Value2 = objHeader;
rHeader =