使用Excel的dll生成excel文档

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;
        }
    }
}