经常看到大家问excel导出问题，现在给大家分享个excel导出源码,实现多sheet表导出,表头样式设置 颜色自定义等

[li358871791]

先前也是发帖问过类似问题,后来在csdn在看到相关解决方案
http://topic.csdn.net/u/20071015/12/a9c49cb4-a83e-44ae-bdca-5b942b920003_2.html（别人的帖子）
但是没有具体的列子和源码,后来自己做出来了，现在跟大家分享下

具体实现是用三放控件 CarlosAg.ExcelXmlWriter.dll 下载地址：http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

可以导出多个sheet表 ，表头样式可以自己自定义 ，功能还是比较强大的，基本可以实现大家现在的要求
封装工作需要三个类来完成：ExcelBook,Excel主处理类。ExcelColumn，Excel表头字段类用于处理自定义表头时使用。ExcelColumnCollection，Excel表头字段集合，用于添加Excel表头。

[li358871791]

导出多个sheet表 ：ExcelMoreBook.cs类

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Collections.Generic;
using CarlosAg.ExcelXmlWriter;


namespace ExcelExp 
{
    /// <summary>
    ///ExcelMoreBook导出多个sheet表
    /// </summary>
    public class ExcelMoreBook
    {
        private Workbook _book = new Workbook();
        private List<DataTable> _dataTable = null;
        private List<string> _title =null;
        private Page _page = null;
        private List<ExcelColumnCollection> _columnNamesCollection = new List<ExcelColumnCollection>();
        private SortedList<string, int> _maxLengthOfField = new SortedList<string, int>();
        private bool _isAutoFitWidth = true;
        private string _Author = "";
        private string _LastAuthor = "";
        private string _Company = "";
        private string _Version = "11.6408";
        /**//// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dt">List(数据源)</param>
        /// <param name="title">List(Excel显示标题)</param>
        public ExcelMoreBook(List<DataTable> dt, List<string> title)
        {
            Page page = (Page)HttpContext.Current.Handler;
            if (dt.Count == 0)
            {
                throw new Exception("数据源为空");
            }
            _dataTable = dt;
            _title = title;
            _page = page;
        }
        /**//// <summary>
        /// 以GridView的Head为标题
        /// </summary>
        /// <param name="row">GridView表头行对象</param>
        public void SetColumnNameFromGridViewHeadRow(GridViewRow row)
        {
            ExcelColumnCollection excelcols = new ExcelColumnCollection();
            _columnNamesCollection.Add(excelcols);
            foreach (TableCell cell in row.Cells)
            {
                excelcols.Add(new ExcelColumn(cell.Text));
            }
        }
        /**//// <summary>
        /// 初始化Excel Workbook
        /// </summary>
        /// <param name="book">book</param>
        private void InitializeBook(Workbook book)
        {
            book.Properties.Author = Author;//作者
            book.Properties.LastAuthor = LastAuthor;//最后作者
            book.Properties.Created = DateTime.Now;//创建时间
            book.Properties.Company = Company;//公司
            book.Properties.Version = Version;//版本
            book.ExcelWorkbook.WindowHeight = 13500;
            book.ExcelWorkbook.WindowWidth = 17100;
            book.ExcelWorkbook.WindowTopX = 360;
            book.ExcelWorkbook.WindowTopY = 75;
            book.ExcelWorkbook.ProtectWindows = false;
            book.ExcelWorkbook.ProtectStructure = false;
        }
        /**//// <summary>
        /// 设置样式
        /// </summary>
        /// <param name="styles">样式集合</param>
        private void SetStyles(WorksheetStyleCollection styles)
        {
            // -----------------------------------------------
            //  Default
            // -----------------------------------------------
            WorksheetStyle Default = styles.Add("Default");
            Default.Name = "Normal";
            Default.Font.FontName = "宋体";
            Default.Font.Size = 12;
            Default.Alignment.Vertical = StyleVerticalAlignment.Center;
            // -----------------------------------------------
            //  TitleStyle
            // -----------------------------------------------
            WorksheetStyle TitleStyle = styles.Add("TitleStyle");
            TitleStyle.Font.Bold = true;
            TitleStyle.Font.FontName = "黑体";
            TitleStyle.Font.Size = 14;
            TitleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            TitleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
            // -----------------------------------------------
            //  FieldStyle
            // -----------------------------------------------
            WorksheetStyle FieldStyle = styles.Add("FieldStyle");
            FieldStyle.Font.Bold = true;
            FieldStyle.Font.FontName = "宋体";
            FieldStyle.Font.Size = 12;
            FieldStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            FieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
            FieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
            FieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
            FieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
            // -----------------------------------------------
            //  LastFieldStyle
            // -----------------------------------------------
            WorksheetStyle LastFieldStyle = styles.Add("LastFieldStyle");
            LastFieldStyle.Font.Bold = true;
            LastFieldStyle.Font.FontName = "宋体";
            LastFieldStyle.Font.Size = 12;
            LastFieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
            LastFieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
            LastFieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
            LastFieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
            // -----------------------------------------------
            //  DataStyle
            // -----------------------------------------------
            WorksheetStyle DataStyle = styles.Add("DataStyle");
            DataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
            DataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
            DataStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
            DataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
        }
        /**//// <summary>
        /// 设置Excel Sheet
        /// </summary>
        /// <param name="sheets">sheets集合</param>
        private void SetSheels(WorksheetCollection sheets)
        {
            for (int i = 0; i < _title.Count;i++ )
            {
                Worksheet sheet = sheets.Add(_title[i].ToString());
                sheet.Table.DefaultRowHeight = 14.25F;
                sheet.Table.DefaultColumnWidth = 54F;
                sheet.Table.FullColumns = 1;
                sheet.Table.FullRows = 1;

                // -----------------------------------------------

                WorksheetRow row = null;
                WorksheetCell cell = null;

                #region 大标题
                row = sheet.Table.Rows.Add();
                row.AutoFitHeight = true;
                row.Height = 30;
                cell = row.Cells.Add();
                cell.StyleID = "TitleStyle";
                cell.Data.Type = DataType.String;
                cell.Data.Text = _title[i];
                cell.MergeAcross = _dataTable[i].Columns.Count - 1;
                #endregion

                foreach (DataColumn dc in _dataTable[i].Columns)//初始化列宽度集合
                {
                    _maxLengthOfField[dc.ColumnName] = 0;
                }

                //-----------------------------------------------字段
                #region 字段标题行

[li358871791]

                if (_columnNamesCollection.Count != 0)
                {
                    for (int i_ = 0; i_ < _columnNamesCollection.Count; i_++)
                    {
                        row = sheet.Table.Rows.Add();
                        row.AutoFitHeight = true;
                        int j = 0;
                        foreach (ExcelColumn ec in _columnNamesCollection[i_])
                        {
                            cell = row.Cells.Add();
                            cell.Data.Text = ec.Name;
                            cell.Data.Type = DataType.String;
                            if (i_ != _columnNamesCollection.Count - 1)
                            {
                                cell.MergeAcross = ec.MergeAcross;
                                cell.StyleID = "FieldStyle";
                            }
                            else//最下层标题行
                            {
                                cell.StyleID = "LastFieldStyle";
                                _maxLengthOfField[_dataTable[i].Columns[j++].ColumnName] =
                                    GetMaxLength(_maxLengthOfField[_dataTable[i].Columns[j++].ColumnName], ec.Name);
                            }
                        }
                    }
                }
                else
                {
                    row = sheet.Table.Rows.Add();
                    row.AutoFitHeight = true;
                    foreach (DataColumn dc in _dataTable[i].Columns)
                    {
                        cell = row.Cells.Add();
                        cell.Data.Text = dc.ColumnName;
                        cell.Data.Type = DataType.String;
                        cell.StyleID = "FieldStyle";
                        _maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dc.ColumnName);
                    }
                }
                #endregion
                // -----------------------------------------------

                #region 数据行
                object dcValueO = null;
                string dcValueS = null;
                foreach (DataRow dr in _dataTable[i].Rows)
                {
                    row = sheet.Table.Rows.Add();
                    row.AutoFitHeight = true;
                    foreach (DataColumn dc in _dataTable[i].Columns)
                    {
                        dcValueO = dr[dc];
                        if (dcValueO == DBNull.Value)
                            dcValueS = string.Empty;
                        else
                            dcValueS = dcValueO.ToString();
                        cell = row.Cells.Add();
                        cell.Data.Text = dcValueS;
                        cell.Data.Type = TypeConvert(dc.DataType);
                        cell.StyleID = "DataStyle";
                        if (_isAutoFitWidth || _columnNamesCollection.Count == 0)
                        {
                            _maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dcValueS);
                        }
                    }
                }
                #endregion
                // -----------------------------------------------

                #region 设置列
                WorksheetColumn column = null;
                if (!_isAutoFitWidth && _columnNamesCollection.Count != 0)
                {
                    foreach (ExcelColumn ec in _columnNamesCollection[_columnNamesCollection.Count - 1])
                    {
                        column = new WorksheetColumn();
                        column.AutoFitWidth = false;
                        column.Width = ec.Width;
                        sheet.Table.Columns.Add(column);
                    }
                }
                else
                {
                    foreach (DataColumn dc in _dataTable[i].Columns)
                    {
                        column = new WorksheetColumn();
                        column.AutoFitWidth = false;
                        column.Width = _maxLengthOfField[dc.ColumnName] * 7;
                        sheet.Table.Columns.Add(column);
                    }
                }
                #endregion
                //  Options
                // -----------------------------------------------
                sheet.Options.Selected = true;
                sheet.Options.ProtectObjects = false;
                sheet.Options.ProtectScenarios = false;
                sheet.Options.Print.PaperSizeIndex = 9;
                sheet.Options.Print.HorizontalResolution = 300;
                sheet.Options.Print.VerticalResolution = 300;
                sheet.Options.Print.ValidPrinterInfo = true;
            }
        
        }
        /**//// <summary>
        /// 向客户端发送Excel下载文档数据
        /// </summary>
        /// <param name="downloadFileName">下载时显示的文件名称</param>
        public void WriteExcelToClient(string downloadFileName)
        {
            string fileName = string.IsNullOrEmpty(downloadFileName) ?
                (string.IsNullOrEmpty(_title[0]) ? "未命名文件" : _title[0]) : downloadFileName;

            InitializeBook(_book);
            SetStyles(_book.Styles);
            SetSheels(_book.Worksheets);

            _book.Save(_page.Response.OutputStream);
            _page.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" + 
                HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls;");
            _page.Response.ContentEncoding = System.Text.Encoding.UTF8;
            _page.Response.Charset = "UTF-8";
            _page.Response.Flush();
            _page.Response.End();
        }
        /**//// <summary>
        /// 向客户端发送Excel下载文档数据
        /// </summary>
        public void WriteExcelToClient()
        {
            WriteExcelToClient(null);
        }
        /**//// <summary>
        /// 数据类型转换
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private DataType TypeConvert(Type type)
        {
            switch (type.Name)
            {
                case "Decimal":
                case "Double":
                case "Single":
                    return DataType.Number;
                case "Int16":
                case "Int32":
                case "Int64":
                case "SByte":
                case "UInt16":
                case "UInt32":
                case "UInt64":
                    return DataType.Number;
                case "String":
                    return DataType.String;
                case "DateTime":
                    return DataType.String;
                default:
                    return DataType.String;
            }
        }
        /**//// <summary>
        /// 添加标题行 集合
        /// </summary>
        /// <param name="exColumnCollection"></param>
        public void AddColumnNamesCollection(ExcelColumnCollection exColumnCollection)
        {
            _columnNamesCollection.Add(exColumnCollection);
        }
        /**//// <summary>
        /// 清楚标题行集合
        /// </summary>
        public void ClearColumnNamesCollection()
        {
            _columnNamesCollection.Clear();
        }
        /**//// <summary>
        /// 列宽是否自适应
        /// </summary>
        public bool IsAutoFitWidth
        {
            get { return _isAutoFitWidth; }
            set { _isAutoFitWidth = value; }
        }
        public string Author
        {
            get { return _Author; }
            set { _Author = value; }
        }
        public string LastAuthor
        {
            get { return _LastAuthor; }
            set { _LastAuthor = value; }
        }
        public string Company
        {
            get { return _Company; }
            set { _Company = value; }
        }
        public string Version
        {
            get { return _Version; }
            set { _Version = value; }
        }
        /**//// <summary>
        /// 获取字段最大宽度函数
        /// </summary>
        /// <param name="oldLength">原来长度</param>
        /// <param name="str">当前字符串</param>
        /// <returns>最大值</returns>
        private int GetMaxLength(int oldLength, string str)
        {
            if (str == null) str = "";
            byte[] bs = System.Text.Encoding.Default.GetBytes(str.Trim());
            int newLength = bs.Length;
            if (oldLength > newLength)
                return oldLength;
            else
                return newLength;
        }
    
    }
}

[relup]

不怎么使用EXCEL

[li358871791]

事先说明上面列子不是本人原创，是参照网上
地址：http://blog.csdn.net/fancy050821/archive/2009/05/25/4213633.aspx
但是这的没有导出多sheet表功能，是我后来改的
在这里拿出来没有其它意思，只是想跟大家分享下，以便其他兄弟碰到
我以前做的时候也是花很多时间在上面

楼上说的孟大哥的那不是这种方法请看清楚
在说下 只是跟大家共享学习下，东西也是我从网上看到的， 已表明出处

[yksyuan]

11楼 误会了。我只是发个地址。
你整理这个 不正好 可以借鉴下嘛（偶是善意的）

[li358871791]

[Quote=引用 12 楼 yksyuan 的回复:]
11楼 误会了。我只是发个地址。
你整理这个 不正好 可以借鉴下嘛（偶是善意的）
[/Quote]
嗯嗯 误会了 不好意思

[心灵彩虹]

我也贴一个，自己做的，导出到excel模板。

 /// <summary>
        /// 导出到excel模板add by gfl 20110414
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExcel_Click(object sender, EventArgs e)
        {
            //检查是否安装excel程序
            if (!codeboolisExcelInstalled())
            {
                MessageBox.Show("当前系统没有发现可执行的Excel文件, 如需使用Excel功能请先安装office", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            for (int colNumb = 0; colNumb < dgvITList.Rows.Count - 1; colNumb++)
            {
                //判断是否有 数量、价格、金额合计没有填写完整的行
                if (dgvITList.Rows[colNumb].Cells["Number"].Value == null || dgvITList.Rows[colNumb].Cells["colRealPrice"].Value == null || dgvITList.Rows[colNumb].Cells["Column17"].Value == null)
                {
                    MessageBox.Show("销售数据未填写完整，请将数据填写完整!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            //以顾客名称加上日期来命名要保存到的文件夹
            string Path1 = Application.StartupPath + "\\SaveExcel";
            string Path2 = Application.StartupPath + "\\ExcelModal";
            if (!Directory.Exists(Path1))//如果不存在就创建SaveExcel 文件夹
            {
                Directory.CreateDirectory(Path1);
            }
            if (!Directory.Exists(Path2))//如果不存在就创建ExcelModal文件夹
            {
                Directory.CreateDirectory(Path2);
            }

            //判断模板文件是否存在？
            string path2;//源文件路径名称
            saveFileDialog1.FileName = cbxCustomer.Text + DateTime.Now.ToString("yyyymmdd") + "销售清单";
            saveFileDialog1.Title = "销售报表存放位置";
            saveFileDialog1.Filter = "excel files(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            //以顾客名称加上日期来命名要保存到的文件夹
            saveFileDialog1.InitialDirectory = Application.StartupPath + "\\SaveExcel";
            saveFileDialog1.RestoreDirectory = true;
            if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            path2 = saveFileDialog1.FileName;
            ExcelFileCopy(path2);//复制模板到用户指定路径

            if (path2 == null)
            {
                MessageBox.Show("Excel模板文件路径不能为空！", "系统信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                return;
            }
            if (!File.Exists(path2))
            {
                MessageBox.Show("指定路径的Excel模板文件不存在！", "系统信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                return;
            }
            templetFile = path2;
            //C#创建Excel文件之取得数据  
            DataTable dtE = GetData();
            if (dtE != null)
            {
                int rowCount = dtE.Rows.Count;
                DataTableToExcel(dtE);
            }
            this.Cursor = Cursors.Default;
        }
        /// <summary> 
        /// 10进制到Excel的26进制的转换函数 
        /// </summary> 
        /// <param name="i"></param> 
        /// <returns></returns> 
        private string convertToCharacter(int i)
        {
            char[] list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            StringBuilder sb = new StringBuilder();
            while ((i - 1) / 26 != 0)
            {
                sb.Append(list[i / 26 - 1]);
                i = i % 26;
            }
            i = (i - 1) % 26;
            sb.Append(list[i]);
            ColNum = sb.ToString();
            return ColNum;
        }

[心灵彩虹]

///   <summary> 
        /// 将DataTable数据写入Excel文件（套用模板并分页）add by gefangliang 20120414
        ///   </summary> 
        ///   <param name="dt"> DataTable </param> 
        ///   <param name="workSheet"> Excel sheet页对象 </param> 
        ///   <param name="top"> 行索引 </param> 
        public void DataTableToExcel(DataTable dt)
        {
            // 创建一个Application对象并使其可见 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = true;
            // 打开模板文件，得到WorkBook对象 
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks._Open(templetFile, missing, missing, missing, missing, missing, missing, missing,
                               missing, missing, missing, missing, missing);
            //Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(missing);
            // 得到WorkSheet对象 
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
            int nameIndex = -1;//名称的Index
            int numIndex = -1;//件数的Index
            int qtyIndex = -1;//数量的Index
            int priceIdex = -1;//价格的Index
            int sumIndex = -1;//金额的Index

            for (int colIndex = 1; colIndex < workSheet.UsedRange.Cells.Columns.Count; colIndex++)//循环读取Excel列
            {
                ColNum = convertToCharacter(colIndex);
                string colomtext = "";
                if (workSheet.get_Range(ColNum + "3", Missing.Value).Value2 != null)
                {
                    colomtext = workSheet.get_Range(ColNum + "3", Missing.Value).Value2.ToString(); //获取字母列名称
                }
                switch (colomtext)//
                {
                    case "&名称":
                        nameIndex = colIndex;
                        workSheet.get_Range(ColNum + "3", Missing.Value).Value2 = "名称";

                        break;
                    case "&件数":
                        numIndex = colIndex;
                        workSheet.get_Range(ColNum + "3", Missing.Value).Value2 = "件数";
                        break;
                    case "&数量":
                        qtyIndex = colIndex;
                        workSheet.get_Range(ColNum + "3", Missing.Value).Value2 = "数量";
                        break;
                    case "&单价":
                        priceIdex = colIndex;
                        workSheet.get_Range(ColNum + "3", Missing.Value).Value2 = "单价";
                        break;
                    case "&金额":
                        sumIndex = colIndex;
                        workSheet.get_Range(ColNum + "3", Missing.Value).Value2 = "金额";
                        break;
                }
            }
            //获取顾客名称
            if (cbxCustomer.Text != null)
            {
                workSheet.Cells[2, 2] = this.cbxCustomer.Text.ToString();
            }
            //获取公司名称
            clsSale cn = new clsSale();
            DataSet dtCompanyName = new DataSet();
            dtCompanyName = cn.GetDataList(21);
            if (dtCompanyName != null && dtCompanyName.Tables[0].Rows.Count != 0)//判读公司名称是否存在
            {//模板写入公司名称
                workSheet.Cells[1, 3] = "公司名称：" + dtCompanyName.Tables[0].Rows[0][0].ToString();
            }
            //获取销售编号
            int saleNum;
            string saleNo;//销售编号
            saleNum = cn.GetdataDetail(2);
            if (cn.salecd == "")
            {
                saleNo = "销售编号：" + "I" + DateTime.Now.ToString("yyyyMMdd") + "000001";
            }
            else if (cn.salecd.Substring(1, 8) != DateTime.Now.ToString("yyyyMMdd"))
            {//今天第一次销售
                saleNo = "销售编号：" + "I" + DateTime.Now.ToString("yyyyMMdd") + "000001";
            }
            else
            {//今天不是第一次销售
                string a = cn.salecd.Substring(0, 8);
                saleNo = "销售编号:" + a + ComLibrary.ToString(ComLibrary.ToInt(cn.salecd.Substring(8, 7)) + 1);
            }
            workSheet.Cells[2, 7] = saleNo;//写入销售编号
            workSheet.Cells[2, 5] = "销售日期:" + DateTime.Now.ToString("yyyy-MM-dd");//写入销售日期
            //绑定qq
            //clsCustomer qq = new clsCustomer();
            //qq.customername = this.cbxCustomer.Text;
            //int rtn = qq.GetdataDetail(1);

            //if (qq.QQ != null && rtn == 0)
            //{
            //    workSheet.Cells[1, 1] = "QQ:" + qq.QQ;
            //}
            int cellCount = workSheet.UsedRange.Cells.Rows.Count - 5;
            int roundCount = 0;//用来判定复制模板次数
            if (dt.Rows.Count % cellCount == 0)//假如模为零
            {
                roundCount = dt.Rows.Count / cellCount - 1;
            }
            else
            {
                roundCount = dt.Rows.Count / cellCount;
            }
            if (dt.Rows.Count < cellCount)//dt行数小于模板行数
            {
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)//循环给模板赋值
                {
                    if (nameIndex != -1)//判断模板是否存在列
                    {
                        workSheet.Cells[rowIndex + 4 + roundCount * 5, nameIndex] = dt.Rows[rowIndex]["名称"].ToString();
                    }
                    if (numIndex != -1)
                    {
                        workSheet.Cells[rowIndex + 4 + roundCount * 5, numIndex] = dt.Rows[rowIndex]["件数"].ToString();
                    }
                    if (qtyIndex != -1)
                    {
                        workSheet.Cells[rowIndex + 4 + roundCount * 5, qtyIndex] = dt.Rows[rowIndex]["数量"].ToString();
                    }
                    if (priceIdex != -1)
                    {
                        workSheet.Cells[rowIndex + 4 + roundCount * 5, priceIdex] = dt.Rows[rowIndex]["单价"].ToString();
                    }
                    if (sumIndex != -1)
                    {
                        workSheet.Cells[rowIndex + 4 + roundCount * 5, sumIndex] = dt.Rows[rowIndex]["金额"].ToString();
                    }
                }
            }
            else//dt行数大于模板行数
            {
                int coun = 0;//标记第几次复制模板
                for (int copyNum = 1; copyNum <= roundCount; copyNum++)
                {
                    //被复制表格的行
                    ((Microsoft.Office.Interop.Excel.Range)workSheet.Rows["1:20", Type.Missing]).Select();
                    ((Microsoft.Office.Interop.Excel.Range)app.Selection).Copy(Type.Missing);
                    string pasteStartRowNo = ((cellCount + 5) * copyNum + 1).ToString();//要复制到区域末尾的行号
                    string pasteEndRowNo = ((cellCount + 5) * (copyNum + 1)).ToString();//要复制到区域末尾的行号
                    string msd = pasteStartRowNo + ":" + pasteEndRowNo;
                    //粘贴刚复制的表格
                    ((Microsoft.Office.Interop.Excel.Range)workSheet.Rows[msd, Type.Missing]).Select();
                    ((Microsoft.Office.Interop.Excel.Worksheet)app.ActiveSheet).Paste(Type.Missing, Type.Missing);
                }
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)//循环给模板赋值
                {
                    if (rowIndex + 1 > cellCount && (rowIndex + 1) % cellCount == 1)//得到是第几次复制表格
                    {
                        ++coun;
                    }
                    if (rowIndex + 1 > cellCount)//给复制的表格赋值
                    {
                        if (nameIndex != -1)//判断模板是否存在列
                        {
                            workSheet.Cells[rowIndex + 4 + coun * 5, nameIndex] = dt.Rows[rowIndex]["名称"].ToString();
                        }
                        if (numIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4 + coun * 5, numIndex] = dt.Rows[rowIndex]["件数"].ToString();
                        }
                        if (qtyIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4 + coun * 5, qtyIndex] = dt.Rows[rowIndex]["数量"].ToString();
                        }
                        if (priceIdex != -1)
                        {
                            workSheet.Cells[rowIndex + 4 + coun * 5, priceIdex] = dt.Rows[rowIndex]["单价"].ToString();
                        }
                        if (sumIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4 + coun * 5, sumIndex] = dt.Rows[rowIndex]["金额"].ToString();
                        }
                    }
                    else//第一次给表格赋值
                    {
                        if (nameIndex != -1)//判断模板是否存在列
                        {
                            workSheet.Cells[rowIndex + 4, nameIndex] = dt.Rows[rowIndex]["名称"].ToString();
                        }
                        if (numIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4, numIndex] = dt.Rows[rowIndex]["件数"].ToString();
                        }
                        if (qtyIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4, qtyIndex] = dt.Rows[rowIndex]["数量"].ToString();
                        }
                        if (priceIdex != -1)
                        {
                            workSheet.Cells[rowIndex + 4, priceIdex] = dt.Rows[rowIndex]["单价"].ToString();
                        }
                        if (sumIndex != -1)
                        {
                            workSheet.Cells[rowIndex + 4, sumIndex] = dt.Rows[rowIndex]["金额"].ToString();
                        }
                    }
                }
            }
            //app.Quit();//关闭excel文件
        }

[心灵彩虹]

  /// <summary> 
        /// 10进制到Excel的26进制的转换函数 
        /// </summary> 
        /// <param name="i"></param> 
        /// <returns></returns> 
        private string convertToCharacter(int i)
        {
            char[] list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            StringBuilder sb = new StringBuilder();
            while ((i - 1) / 26 != 0)
            {
                sb.Append(list[i / 26 - 1]);
                i = i % 26;
            }
            i = (i - 1) % 26;
            sb.Append(list[i]);
            ColNum = sb.ToString();
            return ColNum;
        }
 public void ExcelFileCopy(string path2)
        {
            string path = Application.StartupPath + @"\ExcelModal\销售清单.xls";
            FileInfo fi1 = new FileInfo(path);
            FileInfo fi2 = new FileInfo(path2);
            try
            {
                if (fi2.Exists)
                {
                    fi2.Delete();
                }
                fi1.CopyTo(path2);//复制模板到指定路径
            }
            catch
            {
                Console.WriteLine("模板复制失败！");
            }
        }
        //判断本机是否安装Excel文件方法
        private bool codeboolisExcelInstalled()
        {
            Type type = Type.GetTypeFromProgID("Excel.Application");
            return type != null;
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        private DataTable GetData()
        {
            System.Data.DataTable dtExcel = new DataTable();
            //添加dtExcel列
            dtExcel.Columns.Add("名称", typeof(string));
            dtExcel.Columns.Add("件数", typeof(int));
            dtExcel.Columns.Add("数量", typeof(int));
            dtExcel.Columns.Add("单价", typeof(decimal));
            dtExcel.Columns.Add("金额", typeof(decimal));
            for (int colNum = 0; colNum < dgvITList.Rows.Count - 1; colNum++)
            {
                DataRow dr = dtExcel.NewRow();
                //将datagridview中某行某列的值添加到字段中
                dr["名称"] = dgvITList.Rows[colNum].Cells["Column3"].Value;
                if (dgvITList.Columns["Group1"].Visible == true && dgvITList.Rows[colNum].Cells["Group1"].Value != null)
                {

                    dr["件数"] = ComLibrary.ToInt(dgvITList.Rows[colNum].Cells["Group1"].Value);
                }
                else
                {
                    dr["件数"] = 0;
                }
                dr["数量"] = dgvITList.Rows[colNum].Cells["Number"].Value.ToString().Replace(",", "");
                dr["单价"] = dgvITList.Rows[colNum].Cells["colRealPrice"].Value.ToString().Replace(",", "");
                dr["金额"] = dgvITList.Rows[colNum].Cells["Column17"].Value.ToString().Replace(",", "");
                dtExcel.Rows.Add(dr);//将dgvITList的一行添加到dtExcel的行中
            }
            return dtExcel;
        }

[wang427]

lz,我关心的是你这种方法在没有装OFFICE,装wps的系统中能不能导出啊？

[li358871791]

[Quote=引用 25 楼 wang427 的回复:]
lz,我关心的是你这种方法在没有装OFFICE,装wps的系统中能不能导出啊？
[/Quote]
这个我倒是真没试过 、你可以自己试试

[a09]

绝对 执行不了！

[li358871791]

子夜都来围观了，呵呵

[心灵彩虹]

导出excel也没有说要wps啊。

[li358871791]

[Quote=引用 29 楼 wg4308 的回复:]
绝对 执行不了！
[/Quote]
这么绝对 能讲讲么，

[li358871791]

欢迎大家板砖。。

[骑猪看海]

Microsoft.Office.Interop.Excel才是王者之道，虽然慢了点，但什么样复杂的报表都能做出来，生成各类型图表都不在话下

[KeepMoving]

不错！支持！

[yksyuan]

[Quote=引用 25 楼 wang427 的回复:]
lz,我关心的是你这种方法在没有装OFFICE,装wps的系统中能不能导出啊？
[/Quote]
我发的那个连接 可以
我上次就是 用万网的服务器。没装office 所以发帖问的
孟子E章 前辈回的贴
经过测试 可以

[w294881405]

楼主你的这个怎么用的能不能发个案例给我看看[e-mail]