NDBG_LCS/Kingo.Plugins/KGIS.Plugin.LayerProperty/Helper/ExportExcel.cs

using KGIS.Plugin.LayerProperty.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace KGIS.Plugin.LayerProperty.Helper
{
    public class ExportExcel
    {

        public static void CreateAndAddTitle(string filePath, string sheetName, List<DataColumnEx> lstDataColumnEx, DataTable dtData)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            worksheet.Name = sheetName;
            int colIndex = 0;
            foreach (DataColumnEx col in lstDataColumnEx)
            {
                colIndex++;
                worksheet.Cells[1, colIndex] = col.ChineseName;
            }
            SetFormat(worksheet, lstDataColumnEx, 2, dtData.Rows.Count);
            AddRangeData(worksheet, dtData, 2);
            Marshal.ReleaseComObject(worksheet);
            excel.DisplayAlerts = false;
            workbook.SaveCopyAs(filePath);
            workbook.Close(false, null, null);
            Marshal.ReleaseComObject(workbook);
            excel.Workbooks.Close();
            workbook = null;
        }

        private static void SetFormat(Microsoft.Office.Interop.Excel.Worksheet worksheet, List<DataColumnEx> lstDataColumnEx, int startRow, int rowNumber)
        {
            int colIndex = 0;
            foreach (DataColumnEx col in lstDataColumnEx)
            {
                colIndex++;
                if (col.Type == ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeString)
                {
                    Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[startRow, colIndex], worksheet.Cells[rowNumber + startRow - 1, colIndex]);
                    range.NumberFormatLocal = "@";
                }
            }
        }

        public static void AddRangeData(Microsoft.Office.Interop.Excel.Worksheet worksheet, DataTable dtData, int startRow, bool isAddBK = false)
        {
            int rowNumber = dtData.Rows.Count;
            int columnNumber = dtData.Columns.Count;
            object[,] objData = new object[rowNumber, columnNumber];
            for (int r = 0; r < rowNumber; r++)
            {
                for (int c = 0; c < columnNumber; c++)
                {
                    objData[r, c] = dtData.Rows[r][c];
                }
            }
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[startRow, 1], worksheet.Cells[rowNumber + startRow - 1, columnNumber]);
            if (isAddBK)
            {
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                //for (int i = startRow; i < rowNumber + startRow; i++)
                //{
                //    for (int j = 1; j < columnNumber + 1; j++)
                //    {
                //        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                //        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                //        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                //    }
                //}
            }
            range.Value2 = objData;
        }





    }

}