using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.IO;
using PackingDetailModel = Supplier.Model.PackingDetailModel;
using Microsoft.Office.Interop.Excel;
namespace Supplier.Common
{
public class ExcelDLLHelper
{
private const string FORMAT = "@";
private static string path = HttpContext.Current.Server.MapPath(@"~/Upload/Invoices/" + PageHelper.BillId() + ".xls");
public static void GainOCExcel(HttpRequest request, HttpResponse response, IList<string> dataList, string[] viaType, IList<PackingDetailModel> packList)
{
if (File.Exists(path))
{
DownLoad(request, response);
}
else
{
GenerateOCExcel(dataList, viaType, packList);
DownLoad(request, response);
}
}
private static void DownLoad(HttpRequest request, HttpResponse response)
{
CLibrary.FileOperate.ResponseFile(request, response, PageHelper.BillId() + ".xls", path, 5120);
response.End();
}
//生成Excel文档的关键代码
private static void GenerateOCExcel(IList<string> dataList, string[] viaType, IList<PackingDetailModel> packList)
{
Application app = new Application();
if (app == null)
{
throw new Exception("Unable to open the excel, please check the excel is available or installation.");
}
//解决操作系统不是英文版本的问题
System.Globalization.CultureInfo culture = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);//只生成一个sheet表
Microsoft.Office.Interop.Excel.Range range;
worksheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//表头
int rc = packList.Count;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[18 + rc + 6, 18]);
range.get_Range(worksheet.Cells[1, 1], worksheet.Cells[3, 1]).ColumnWidth = 0.6;//设置第一列的列宽
range.get_Range(worksheet.Cells[1, 18], worksheet.Cells[3, 18]).ColumnWidth = 0.6;//设置最后一列的列宽
range.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 18]).RowHeight = 30;//设置第一行的行高
//设置边框
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlDouble;//设置为xlDouble可以解决A列左边框无线条的问题
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThick;
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlDouble;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThick;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlDouble;//设置为xlDouble可以解决最顶行无上边框的问题
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThick;
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlDouble;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThick;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
#region Header of excel
//First row of header
range = worksheet.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 5]);
range.Font.Size = 18;//设置字体大小
range.Font.Bold = true;//设置字体是否加粗
range.MergeCells = true;//设置单元格是否合并
range.Font.Name = "Bookman Old Style";//设置字体样式
worksheet.Cells[1, 2] = "PRIME MODERN LIMITED.";//设置单元格显示内容
//Second row of header
range = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[2, 3]);
worksheet.Cells[2, 2] = "BILL TO:";
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
//Third row of header
string[] billTo = StringHelper.StringToArray(dataList[0], ';');
range = worksheet.get_Range(worksheet.Cells[3, 2], worksheet.Cells[3, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;//设置内容水平居左显示
worksheet.Cells[3, 2] = billTo[0];
//Fourth row of header
range = worksheet.get_Range(worksheet.Cells[4, 2], worksheet.Cells[4, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;//设置内容垂直居底端显示
worksheet.Cells[4, 2] = billTo[1];
range = worksheet.get_Range(worksheet.Cells[4, 11], worksheet.Cells[4, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[4, 11] = DataMapTable.Line(dataList[4]);
range = worksheet.get_Range(worksheet.Cells[4, 13], worksheet.Cells[4, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[4, 13] = dataList[2];
//Fifth row of header
range = worksheet.get_Range(worksheet.Cells[5, 2], worksheet.Cells[5, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
string billTo2 = (billTo.Length > 2) ? billTo[2] : " ";
worksheet.Cells[5, 2] = billTo2;
range = worksheet.get_Range(worksheet.Cells[5, 11], worksheet.Cells[5, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[5, 11] = DataMapTable.Via(dataList[4]);
range = worksheet.get_Range(worksheet.Cells[5, 13], worksheet.Cells[5, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[5, 13] = dataList[4];
//Sixth row of header
range = worksheet.get_Range(worksheet.Cells[6, 2], worksheet.Cells[6, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
string billTo3 = (billTo.Length > 3) ? billTo[3] : " ";
worksheet.Cells[6, 2] = billTo3;
range = worksheet.get_Range(worksheet.Cells[6, 11], worksheet.Cells[6, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[6, 11] = DataMapTable.Date(dataList[4]);
range = worksheet.get_Range(worksheet.Cells[6, 13], worksheet.Cells[6, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
range.NumberFormat = FORMAT;
worksheet.Cells[6, 13] = DateHelper.EuropeanDate(dataList[3]);
//Seventh row of header
range = worksheet.get_Range(worksheet.Cells[7, 11], worksheet.Cells[7, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[7, 11] = viaType[0];
range = worksheet.get_Range(worksheet.Cells[7, 13], worksheet.Cells[7, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[7, 13] = dataList[10];
//Eighth row of header
range = worksheet.get_Range(worksheet.Cells[8, 2], worksheet.Cells[8, 3]);
worksheet.Cells[8, 2] = "SENT TO:";
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range = worksheet.get_Range(worksheet.Cells[8, 11], worksheet.Cells[8, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[8, 11] = viaType[1];
range = worksheet.get_Range(worksheet.Cells[8, 13], worksheet.Cells[8, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[8, 13] = dataList[11];
//Ninth row of header
string[] sentTo = StringHelper.StringToArray(dataList[1], ';');
range = worksheet.get_Range(worksheet.Cells[9, 2], worksheet.Cells[9, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[9, 2] = sentTo[0];
range = worksheet.get_Range(worksheet.Cells[9, 11], worksheet.Cells[9, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[9, 11] = "FOB";
range = worksheet.get_Range(worksheet.Cells[9, 13], worksheet.Cells[9, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[9, 13] = dataList[5];
//Tenth row of header
range = worksheet.get_Range(worksheet.Cells[10, 2], worksheet.Cells[10, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[10, 2] = sentTo[1];
range = worksheet.get_Range(worksheet.Cells[10, 11], worksheet.Cells[10, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[10, 11] = "COUNTRY OF ORIGIN";
range = worksheet.get_Range(worksheet.Cells[10, 13], worksheet.Cells[10, 14]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[10, 13] = "China";
//Eleventh row of header
range = worksheet.get_Range(worksheet.Cells[11, 2], worksheet.Cells[11, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
string sentTo2 = (sentTo.Length > 2) ? sentTo[2] : " ";
worksheet.Cells[11, 2] = sentTo2;
range = worksheet.get_Range(worksheet.Cells[11, 11], worksheet.Cells[11, 12]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeRight(range);
worksheet.Cells[11, 11] = "CARRIER";
range = worksheet.get_Range(worksheet.Cells[11, 13], worksheet.Cells[11, 17]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[11, 13] = "ABC European Air & Sea Cargo Distribution GmbH";
//Twelfth row of header
range = worksheet.get_Range(worksheet.Cells[12, 2], worksheet.Cells[12, 3]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
string sentTo3 = (sentTo.Length > 3) ? sentTo[3] : " ";
worksheet.Cells[12, 2] = sentTo3;
//Fourteenth row of header
range = worksheet.get_Range(worksheet.Cells[14, 2], worksheet.Cells[14, 5]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeBottom(range);
worksheet.Cells[14, 2] = dataList[7];
range = worksheet.get_Range(worksheet.Cells[14, 8], worksheet.Cells[14, 11]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeBottom(range);
worksheet.Cells[14, 8] = dataList[8];
range = worksheet.get_Range(worksheet.Cells[14, 14], worksheet.Cells[14, 17]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
EdgeBottom(range);
worksheet.Cells[14, 14] = dataList[12];
//Fifteenth row of header
range = worksheet.get_Range(worksheet.Cells[15, 2], worksheet.Cells[15, 5]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[15, 2] = "INVOICE NO.";
range = worksheet.get_Range(worksheet.Cells[15, 8], worksheet.Cells[15, 11]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[15, 8] = DataMapTable.No(dataList[4]);
range = worksheet.get_Range(worksheet.Cells[15, 14], worksheet.Cells[15, 17]);
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignBottom;
worksheet.Cells[15, 14] = "TOTAL CBM";
#endregion
#region Header of table
range = worksheet.get_Range(worksheet.Cells[17, 2], worksheet.Cells[17, 17]);
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin;
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
string[] headerArray = { "PL#", "PO#", "ITEM", ConstantValue.TARIFF, "QTY", "QTY PER", "NO. OF", "GROSS", "NET", "Ex Works", "Shipping", "Arrival", "SENT", "CARTON", "CARTONS", "WEIGHT", "WEIGHT", "DATE", "DATE", "DATE" };
for (int c = 2; c < 6; c++)
{
range = worksheet.get_Range(worksheet.Cells[17, c], worksheet.Cells[18, c]);
MergeSizeName(range);
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
EdgeLeft(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[17, c] = headerArray[c - 2];
}
range = worksheet.get_Range(worksheet.Cells[17, 6], worksheet.Cells[17, 6]);
range.Font.Bold = true;
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
worksheet.Cells[17, 6] = ConstantValue.ABFORTHE;
range = worksheet.get_Range(worksheet.Cells[18, 6], worksheet.Cells[18, 6]);
range.Font.Bold = true;
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[18, 6] = ConstantValue.PACKAGING;
range = worksheet.get_Range(worksheet.Cells[17, 7], worksheet.Cells[17, 9]);
range.Font.Bold = true;
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[17, 7] = "MEASUREMENT(cm)";
range = worksheet.get_Range(worksheet.Cells[18, 7], worksheet.Cells[18, 7]);
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[18, 7] = "length";
range = worksheet.get_Range(worksheet.Cells[18, 8], worksheet.Cells[18, 8]);
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[18, 8] = "width";
range = worksheet.get_Range(worksheet.Cells[18, 9], worksheet.Cells[18, 9]);
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[18, 9] = "high";
for (int c = 10; c < 18; c++)
{
range = worksheet.get_Range(worksheet.Cells[17, c], worksheet.Cells[17, c]);
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
worksheet.Cells[17, c] = headerArray[c - 6];
range = worksheet.get_Range(worksheet.Cells[18, c], worksheet.Cells[18, c]);
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[18, c] = headerArray[c + 2];
}
#endregion
#region Content of table
string arrivalDate = DateHelper.ArrivalDate(dataList[3], dataList[4]);
for (int r = 0; r < rc; r++)
{
range = worksheet.get_Range(worksheet.Cells[19 + r, 2], worksheet.Cells[19 + r, 2]);
SizeNameCenter(range);
range.EntireColumn.AutoFit();//单元格根据内容自适应宽度
EdgeLeft(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 2] = dataList[7];
range = worksheet.get_Range(worksheet.Cells[19 + r, 3], worksheet.Cells[19 + r, 3]);
SizeNameCenter(range);
range.EntireColumn.AutoFit();
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 3] = packList[r].PO;
range = worksheet.get_Range(worksheet.Cells[19 + r, 4], worksheet.Cells[19 + r, 4]);
SizeNameCenter(range);
range.EntireColumn.AutoFit();
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 4] = packList[r].Item;
range = worksheet.get_Range(worksheet.Cells[19 + r, 5], worksheet.Cells[19 + r, 5]);
SizeNameCenter(range);
range.EntireColumn.AutoFit();
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 5] = "94059900";
range = worksheet.get_Range(worksheet.Cells[19 + r, 6], worksheet.Cells[19 + r, 6]);
SizeNameCenter(range);
range.EntireColumn.AutoFit();
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 6] = packList[r].Code;
range = worksheet.get_Range(worksheet.Cells[19 + r, 7], worksheet.Cells[19 + r, 7]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 7] = packList[r].Length;
range = worksheet.get_Range(worksheet.Cells[19 + r, 8], worksheet.Cells[19 + r, 8]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 8] = packList[r].Width;
range = worksheet.get_Range(worksheet.Cells[19 + r, 9], worksheet.Cells[19 + r, 9]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 9] = packList[r].High;
range = worksheet.get_Range(worksheet.Cells[19 + r, 10], worksheet.Cells[19 + r, 10]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 10] = packList[r].QuanitySent;
range = worksheet.get_Range(worksheet.Cells[19 + r, 11], worksheet.Cells[19 + r, 11]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 11] = packList[r].QtyPerCarton;
range = worksheet.get_Range(worksheet.Cells[19 + r, 12], worksheet.Cells[19 + r, 12]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 12] = packList[r].Cartons;
range = worksheet.get_Range(worksheet.Cells[19 + r, 13], worksheet.Cells[19 + r, 13]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 13] = CalculateHelper.Weight(packList[r].GrossWeight.ToString(), packList[r].Cartons.ToString());
range = worksheet.get_Range(worksheet.Cells[19 + r, 14], worksheet.Cells[19 + r, 14]);
SizeNameCenter(range);
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 14] = CalculateHelper.Weight(packList[r].NetWeight.ToString(), packList[r].Cartons.ToString());
range = worksheet.get_Range(worksheet.Cells[19 + r, 15], worksheet.Cells[19 + r, 15]);
SizeNameCenter(range);
range.NumberFormat = FORMAT;//格式化内容,如数字,日期
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 15] = (packList[r].ExpectWorkDate.ToString().IndexOf("0001") > -1) ? "" : DateHelper.EuropeanDate(packList[r].ExpectWorkDate.ToString());
range = worksheet.get_Range(worksheet.Cells[19 + r, 16], worksheet.Cells[19 + r, 16]);
SizeNameCenter(range);
range.NumberFormat = FORMAT;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 16] = DateHelper.EuropeanDate(dataList[3]);
range = worksheet.get_Range(worksheet.Cells[19 + r, 17], worksheet.Cells[19 + r, 17]);
SizeNameCenter(range);
range.NumberFormat = FORMAT;
EdgeRight(range);
EdgeBottom(range);
worksheet.Cells[19 + r, 17] = arrivalDate;
}
//There is add an empty row.
for (int c = 2; c < 18; c++)
{
range = worksheet.get_Range(worksheet.Cells[19 + rc, c], worksheet.Cells[19 + rc, c]);
EdgeLeft(range);
EdgeBottom(range);
if (c == 17)
{
EdgeRight(range);
}
}
#endregion
#region Footer of table
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 2], worksheet.Cells[19 + rc + 1, 9]);
range.Font.Bold = true;
range.Font.Italic = true;
MergeSizeName(range);
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
EdgeLeft(range);
EdgeThickBottom(range);
worksheet.Cells[19 + rc + 1, 2] = "TOTALS:";
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 10], worksheet.Cells[19 + rc + 1, 10]);
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
EdgeLeft(range);
EdgeThickBottom(range);
worksheet.Cells[19 + rc + 1, 10] = HttpContext.Current.Session["totalQuantity"];
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 11], worksheet.Cells[19 + rc + 1, 11]);
EdgeLeft(range);
EdgeThickBottom(range);
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 12], worksheet.Cells[19 + rc + 1, 12]);
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
EdgeLeft(range);
EdgeThickBottom(range);
worksheet.Cells[19 + rc + 1, 12] = HttpContext.Current.Session["totalCartons"];
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 13], worksheet.Cells[19 + rc + 1, 13]);
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
EdgeLeft(range);
EdgeThickBottom(range);
worksheet.Cells[19 + rc + 1, 13] = HttpContext.Current.Session["totalGrossWeight"];
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, 14], worksheet.Cells[19 + rc + 1, 14]);
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
EdgeLeft(range);
EdgeThickBottom(range);
worksheet.Cells[19 + rc + 1, 14] = HttpContext.Current.Session["totalNetWeight"];
for (int c = 15; c < 18; c++)
{
range = worksheet.get_Range(worksheet.Cells[19 + rc + 1, c], worksheet.Cells[19 + rc + 1, c]);
EdgeLeft(range);
EdgeThickBottom(range);
if (c == 17)
{
EdgeRight(range);
}
}
#endregion
#region Footer of excel
range = worksheet.get_Range(worksheet.Cells[19 + rc + 3, 10], worksheet.Cells[19 + rc + 3, 17]);
range.Font.Size = 9;
range.NumberFormat = FORMAT;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
EdgeBottom(range);
worksheet.Cells[19 + rc + 3, 10] = dataList[13];
string checkDate = DateHelper.EuropeanDate(dataList[3]);
worksheet.Cells[19 + rc + 3, 14] = checkDate;
worksheet.Cells[19 + rc + 3, 17] = checkDate;
range = worksheet.get_Range(worksheet.Cells[19 + rc + 4, 2], worksheet.Cells[19 + rc + 4, 4]);
range.MergeCells = true;
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Italic = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Cells[19 + rc + 4, 2] = "Packing Meterial:Card Board Box";
range = worksheet.get_Range(worksheet.Cells[19 + rc + 4, 10], worksheet.Cells[19 + rc + 4, 11]);
range.MergeCells = true;
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Cells[19 + rc + 4, 10] = "CHECKED BY";
range = worksheet.get_Range(worksheet.Cells[19 + rc + 4, 12], worksheet.Cells[19 + rc + 4, 17]);
range.Font.Size = 9;
range.Font.Name = "Bookman Old Style";
worksheet.Cells[19 + rc + 4, 14] = "DATE";
worksheet.Cells[19 + rc + 4, 17] = "DATE";
range = worksheet.get_Range(worksheet.Cells[19 + rc + 5, 2], worksheet.Cells[19 + rc + 5, 4]);
range.MergeCells = true;
range.Font.Size = 9;
range.Font.Bold = true;
range.Font.Italic = true;
range.Font.Name = "Bookman Old Style";
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Cells[19 + rc + 5, 2] = "No Solid Wood packing Material Used!!!";
#endregion
try
{//保存excel文档
workbook.Saved = true;
workbook.SaveCopyAs(path);
}
catch (Exception ex)
{
throw new Exception("An error occurred when the export file, the file may be open!" + ex.Message);
}
finally
{
//Excel release the corresponding objects
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
app.Application.Workbooks.Close();
app.Quit();
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
KillAllExcel();
}
}
private static void KillAllExcel()
{
List<System.Diagnostics.Process> excelProList = GetExcelProcess();
for (int i = 0; i < excelProList.Count; i++)
{
excelProList[i].Kill();
}
}
private static List<System.Diagnostics.Process> GetExcelProcess()
{
System.Diagnostics.Process[] processArray = System.Diagnostics.Process.GetProcesses();
List<System.Diagnostics.Process> excelProList = new List<System.Diagnostics.Process>();
for (int i = 0; i < processArray.Length; i++)
{
if (processArray[i].ProcessName.ToLower().Equals("EXCEL"))
{
excelProList.Add(processArray[i]);
}
}
return excelProList;
}
private static void SizeNameCenter(Microsoft.Office.Interop.Excel.Range range)
{
SizeName(range, 8);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
private static void MergeSizeName(Microsoft.Office.Interop.Excel.Range range)
{
range.MergeCells = true;
SizeName(range, 9);
}
private static void SizeName(Microsoft.Office.Interop.Excel.Range range, int size)
{
range.Font.Size = size;
range.Font.Name = "Bookman Old Style";
}
private static void EdgeLeft(Microsoft.Office.Interop.Excel.Range range)
{
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin;
range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
}
private static void EdgeBottom(Microsoft.Office.Interop.Excel.Range range)
{
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
}
private static void EdgeRight(Microsoft.Office.Interop.Excel.Range range)
{
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin;
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
}
private static void EdgeThickBottom(Microsoft.Office.Interop.Excel.Range range)
{
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThick;
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic;
}
}
}
使用Excel的dll生成excel文档
最新推荐文章于 2020-02-26 21:11:22 发布