RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.BLL/FileUtil.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;
using ICSSoft.Frame.Data.Entity;

namespace ICSSoft.Frame.Data.BLL
{
    public class FileUtil
    {

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="data"></param>
        /// <remarks></remarks>

        public static void exportToExcelFile(string fileName, List<FormReadExcelUIModelColumns> colNameList)
        {
            Application oExcel = new Application();
            Workbooks workbooks = oExcel.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
            try
            {
                worksheet.Name = "导入模板";
                Range range;
                for (int i = 0; i < colNameList.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = colNameList[i].columnsName;
                    if (colNameList[i].NotNull == true)
                    {
                        range = (Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 8;
                    }
                }
                //oExcel.Visible = true;

                oExcel.ActiveWindow.SplitRow = 1;			    //锁定第四行
                //oExcel.ActiveWindow.SplitColumn = 1;			    //锁定第一列
                oExcel.ActiveWindow.FreezePanes = true;		        //冻结锁定区域
                oExcel.ActiveSheet.rows(1).Font.Bold = true;		        //首行加粗
                oExcel.ActiveSheet.columns.autofit();		        //单元格自动适应宽度
                oExcel.ActiveSheet.columns.HorizontalAlignment = 3;	        //单元格内容水平居中

                workbook.SaveAs(fileName);
                workbook.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("导出Excel文件：" + ex.Message);
            }

            oExcel = null;
            workbook = null;
            worksheet = null;

        }

        public static void exportToExcelFileFromDataTable(string fileName, System.Data.DataTable dt, params string[] readonlyColumNames)
        {
            Application excelApp = new Application();
            Workbooks workbooks = excelApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
            try
            {
                worksheet.Name = "导入模板";
                Range range;
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    worksheet.Cells[1, c + 1] = dt.Columns[c].ColumnName;
                    range = (Range)worksheet.Cells[1, c + 1];
                    range.Interior.ColorIndex = 8;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string v = "";
                        if (dt.Rows[i][j] == DBNull.Value || dt.Rows[i][j] == null)
                        {
                            v = "";
                        }
                        else
                        {
                            if (dt.Columns[j].DataType == typeof(DateTime))
                            {
                                v = Convert.ToDateTime(dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else
                            {
                                v = dt.Rows[i][j].ToString();
                            }
                        }
                        excelApp.Cells[i + 2, j + 1] = v;
                    }
                }


                //for (int i = 0; i < colNameList.Count; i++)
                //{
                //    worksheet.Cells[1, i + 1] = colNameList[i].columnsName;
                //    if (colNameList[i].NotNull == true)
                //    {
                //        range = (Range)worksheet.Cells[1, i + 1];
                //        range.Interior.ColorIndex = 8;
                //    }
                //}

                excelApp.ActiveWindow.SplitRow = 1;			                            //锁定第1行
                //excelApp.ActiveWindow.SplitColumn = 1;			                    //锁定第1列
                excelApp.ActiveWindow.FreezePanes = true;		                        //冻结锁定区域
                excelApp.ActiveSheet.rows(1).Font.Bold = true;		                    //首行加粗
                excelApp.ActiveSheet.columns.autofit();		                            //单元格自动适应宽度
                excelApp.ActiveSheet.columns.HorizontalAlignment = 3;	        //单元格内容水平居中
                excelApp.Visible = true;                                                            //设置Excel可见
                workbook.SaveAs(fileName);
                workbook.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("导出Excel文件：" + ex.Message);
            }

            excelApp = null;
            workbook = null;
            worksheet = null;

        }
    }
}