c# 導出excell 改變標題顏色

1.引入類庫 C1.C1Excel.2.dll

2.建立2個實體類，只要拷貝就可以。

A。XlsMapEntry 數據庫表字段與excell 標題對應類

　　代碼如下：

 [Serializable]
    public struct XlsMapEntry
    {
        private string excelColumn;
        private string tableColumn;

        public string ExcelColumn
        {
            get { return this.excelColumn; }
            set { this.excelColumn = value; }
        }

        public string TableColumn
        {
            get { return this.tableColumn; }
            set { this.tableColumn = value; }
        }

        public XlsMapEntry(string excelColumn, string tableColumn)
        {
            this.excelColumn = excelColumn;
            this.tableColumn = tableColumn;
        }
    }

B.建立一個集合類，用於放這些實體

代碼如下：

 public class XlsMapDictionary : IEnumerable
    {
        private List<XlsMapEntry> items;
        private Int32 ItemsInUse = 0;

        public XlsMapDictionary()
        {
            items = new List<XlsMapEntry>();
        }

        public bool IsReadOnly { get { return false; } }
        public bool Contains(string tableColumn)
        {
            Int32 index;
            return TryGetIndexOfKey(tableColumn, out index);
        }
        public bool IsFixedSize { get { return false; } }
        public void Remove(string tableColumn)
        {
            if (tableColumn.Equals(string.Empty)) throw new ArgumentNullException("control");
            Int32 index;
            if (TryGetIndexOfKey(tableColumn, out index))
            {
                items.RemoveAt(index);
                ItemsInUse--;
            }
            else
            {

            }
        }
        public void Clear()
        {
            items.Clear();
            ItemsInUse = 0;
        }
        public void Add(string excelColumn, string tableColumn)
        {
            items.Add(new XlsMapEntry(excelColumn, tableColumn));
            ItemsInUse++;
        }
        public ICollection ExcelColumns
        {
            get
            {
                Object[] excelColumns = new Object[ItemsInUse];
                for (Int32 n = 0; n < ItemsInUse; n++)
                    excelColumns[n] = items[n].ExcelColumn;
                return excelColumns;
            }
        }
        public ICollection TableColumns
        {
            get
            {
                Object[] tableColumns = new Object[ItemsInUse];
                for (Int32 n = 0; n < ItemsInUse; n++)
                    tableColumns[n] = items[n].TableColumn;
                return tableColumns;
            }
        }
        public object this[int index]
        {
            get
            {
                return items[index];
            }

            set
            {

            }
        }

        private Boolean TryGetIndexOfKey(string tableColumn, out Int32 index)
        {
            for (index = 0; index < ItemsInUse; index++)
            {
                if (items[index].TableColumn.Equals(tableColumn)) return true;
            }

            return false;
        }

        private class XlsMapDictionaryEnumerator : IEnumerator
        {
            XlsMapEntry[] items;
            Int32 index = -1;

            public XlsMapDictionaryEnumerator(XlsMapDictionary xmd)
            {
                items = new XlsMapEntry[xmd.Count];
                xmd.items.CopyTo(items);
            }

            public Object Current { get { XlsMapIndex(); return items[index]; } }

            public XlsMapEntry Entry
            {
                get { return (XlsMapEntry)Current; }
            }

            public Object ExcelColumn { get { XlsMapIndex(); return items[index].ExcelColumn; } }

            public Object TableColumn { get { XlsMapIndex(); return items[index].TableColumn; } }

            public Boolean MoveNext()
            {
                if (index < items.Length - 1) { index++; return true; }
                return false;
            }

            private void XlsMapIndex()
            {
                if (index < 0 || index >= items.Length)
                    throw new InvalidOperationException("Enumerator is before or after the collection.");
            }

            public void Reset()
            {
                index = -1;
            }
        }
        public IEnumerator GetEnumerator()
        {
            return new XlsMapDictionaryEnumerator(this);
        }

        #region ICollection Members
        public bool IsSynchronized { get { return false; } }
        public object SyncRoot { get { throw new NotImplementedException(); } }
        public int Count { get { return ItemsInUse; } }
        public void CopyTo(List<XlsMapEntry> xme, int index)
        {
            XlsMapEntry[] array = new XlsMapEntry[xme.Count];
            xme.CopyTo(array);
        }
        #endregion

        #region IEnumerable Members
        IEnumerator IEnumerable.GetEnumerator()
        {
            return ((XlsMapDictionary)this).GetEnumerator();
        }
        #endregion
    }

3.建立一個操作類ExcellToFile

代碼如下：

class ExcellToFile
    {
        /// <summary>
        /// 產生Excel文檔
        /// </summary>
        /// <param name="dataset">資料來源</param>
        public static string WriteExcel(DataSet dataSet, XlsMapDictionary xlsMapDictionary, string fullPath)
        {
            C1XLBook xlsBook = new C1XLBook();
            int intMaxExcelCount = ExcelMax();

            try
            {
                if (dataSet != null)
                {
                    for (int i = 0; i < dataSet.Tables.Count; i++)
                    {
                        DataTable dataTable = dataSet.Tables[i];
                        XLSheet xlsSheet = xlsBook.Sheets[i];
                        XLStyle xlsStyle = new XLStyle(xlsBook);

                        // Header
                        int j = 0;
                        foreach (XlsMapEntry entry in xlsMapDictionary)
                        {
                            xlsSheet[i, j].Value = entry.ExcelColumn;
                            xlsStyle.BackColor = System.Drawing.Color.LightGray;
                            xlsSheet[i, j].Style = xlsStyle;
                            j++;
                        }

                        // Context
                        int y = 1;
                        int intMaxRow = 0;
                        foreach (DataRow dataRow in dataTable.Rows)
                        {
                            intMaxRow += 1;
                            if (intMaxRow > intMaxExcelCount)
                            {
                                break;
                            }
                            int x = 0;
                            foreach (XlsMapEntry entry in xlsMapDictionary)
                            {
                                if (dataTable.Columns[entry.TableColumn].DataType == typeof(DateTime))
                                {
                                    if (dataRow[entry.TableColumn] != DBNull.Value)
                                        xlsSheet[y, x].Value = Convert.ToDateTime(dataRow[entry.TableColumn]).ToString("yyyy/MM/dd HH:mm:ss");
                                    else
                                        xlsSheet[y, x].Value = "";
                                }
                                else
                                {
                                    xlsSheet[y, x].Value = dataRow[entry.TableColumn];
                                }
                                x++;
                            }
                            y++;
                        }
                        AutoSizeExcelColumns(xlsSheet);
                    }
                }
                xlsBook.Save(fullPath);
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return fullPath;
        }



        /// <summary>
        /// 調整Excel中每欄的最適寬度
        /// </summary>
        /// <param name="sheet">來源Excel</param>
        private static void AutoSizeExcelColumns(XLSheet sheet)
        {
            using (System.Drawing.Graphics graphics = System.Drawing.Graphics.FromHwnd(IntPtr.Zero))
            {
                for (int c = 0; c < sheet.Columns.Count; c++)
                {
                    int colWidth = -1;
                    for (int r = 0; r < sheet.Rows.Count; r++)
                    {
                        object value = sheet[r, c].Value;
                        if (value != null)
                        {
                            // get value (unformatted at this point)
                            string text = value.ToString();

                            // format value if cell has a style with format set
                            XLStyle s = sheet[r, c].Style;
                            if (s != null && s.Format.Length > 0 && value is IFormattable)
                            {
                                string fmt = XLStyle.FormatXLToDotNet(s.Format);
                                text = ((IFormattable)value).ToString(fmt, System.Globalization.CultureInfo.CurrentCulture);
                            }

                            // get font (default or style)
                            System.Drawing.Font font = sheet.Book.DefaultFont;
                            if (s != null && s.Font != null)
                                font = s.Font;

                            // measure string (add a little tolerance)
                            System.Drawing.Size sz = System.Drawing.Size.Ceiling(graphics.MeasureString(text + "x", font));

                            // keep widest so far
                            if (sz.Width > colWidth)
                                colWidth = sz.Width;
                        }
                    }

                    // done measuring, set column width
                    if (colWidth > -1)
                        sheet.Columns[c].Width = C1XLBook.PixelsToTwips(colWidth);
                }
            }
        }

        /// <summary>
        /// 取得Excel限制筆數
        /// </summary>
        /// <param name="sheet">來源Excel</param>
        private static int ExcelMax()
        {

                    return 100;
        }
    }

4。程式中調用

代碼如下：

                System.Windows.Forms.SaveFileDialog saveFile = new System.Windows.Forms.SaveFileDialog();
                saveFile.Filter = "XLS|*.xls";
                saveFile.FileName ="工單"+ DateTime.Now.ToString("yyyy-MM-dd");
                if (System.Windows.Forms.DialogResult.OK == saveFile.ShowDialog()) {
                    var fileName = saveFile.FileName;
//定義欄位
                     TECOSFIS.App_Code.XlsMapDictionary xlsMapDictionary=new App_Code.XlsMapDictionary();
                     xlsMapDictionary.Add("工單", "WKORID");
                     xlsMapDictionary.Add("機種", "ModelsNAME");
                     xlsMapDictionary.Add("機種數量", "POTOTAIL");
                     xlsMapDictionary.Add("平均效率", "Efficiency");
//ds 為數據集合
//filename 為生成的文件名
                     ExcellToFile.WriteExcel(ds, xlsMapDictionary, fileName);
                }

5.說明：

上面調用代碼，是在cs結構中操作的，如果是web，基本一樣。只是再加一個下載程式。代碼如下：

        /// <summary>
        /// 查詢結果Excel下載 For DataSet
        /// </summary>
        protected virtual void DownloadExcel(XlsMapDictionary xlsMapDictionary, DataSet ds)
        {
            //string sql = string.Empty;
            //DataSet ds = null;
            try
            {
                //sql = (string)ViewState[ThisIdentity.UserId + this.Title + "sql"];
                if (ds != null)
                {
                    //ds = this.Query(sql);
                    string fullPath = WriteExcel(ds, xlsMapDictionary);
                    string fileName = this.Title + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    Response.ClearHeaders();
                    Response.Clear();
                    Response.Expires = 0;
                    Response.Buffer = true;
                    Response.AddHeader("Accept-Language", currentCulture);
                    string tmp = "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(fileName));
                    Response.AddHeader("content-disposition", tmp);
                    Response.ContentType = "Application/octet-stream";
                    byte[] theData = System.IO.File.ReadAllBytes(fullPath);
                    //Response.BinaryWrite(File.ReadAllBytes(fullPath));
                    Response.BinaryWrite(theData);
                    Response.Flush();
                    Response.Close();
                    File.Delete(fullPath);
                    /*
                    * Response.End()會產生ThreadAbortException 異常,但是不加http流會加上"<html><title>  </title>.....
                    * 之類的html符號.
                    */
                    try
                    {
                        //Response.End();
                        Response.Clear();
                    }
                    catch (System.Threading.ThreadAbortException tae)
                    {
                    }
                }
            }
            catch (SqlException sqlex)
            {
                logger.ErrorException(sqlex.ToString(), sqlex);
                throw sqlex;
            }
            catch (Exception ex)
            {
                logger.ErrorException(ex.ToString(), ex);
                throw ex;
            }
        }