asp.net数据导出到excel中,如何用C#实现按内容合并单元格

Question

asp.net数据导出到excel中,如何用C#实现按内容合并单元格

Answer 1

    /// <summary>
    /// download file
    /// </summary>
    /// <param name="filePath">file path</param>
    private void download(string filePath)
    {
        // clear response
        Response.ClearContent();
        Response.Buffer = true;
        // set character
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");        // file type and downLoadFileName
        Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.Ticks.ToString() + ".xls");
        // set file type
        Response.ContentType = "application/ms-excel";        try
        {
            // write file to client
            Response.WriteFile(filePath);
            //flush
            Response.Flush();
            Response.End();
        }
        catch
        {
        }
        finally
        {
            // delete the temporary file
            try
            {
                File.Delete(filePath);
            }
            catch
            { }
        }
    }/// <summary>
    /// generate a sheet of the excel
    /// </summary>
    /// <param name="worksheet">work sheet</param>
    /// <param name="gridView">grid view</param>
    /// <param name="title">sheet's title</param>
    /// <param name="lastLineHighligt"></param>
    private void generateSheet(Excel.Worksheet worksheet, GridView gridView, string title, bool lastLineHighligt)
    {
        int rowCount = gridView.Rows.Count;      // row count
        int colCount = gridView.Columns.Count;   // column count
        int titleFontSize = 12;                  // font size of title
        int contextFontSize = 10;                // font size of context
        int columnWidth = 15;                    // width of the column        // set work sheet name
        worksheet.Name = title;        // set print date
        Excel.Range printDate = worksheet.Cells[1, 1] as Excel.Range;
        printDate.Value2 = Resources.ECCResource.PrintDate;
        setCharacterFormat(printDate, contextFontSize, columnWidth);
        Excel.Range printDateValue = worksheet.Cells[1, 2] as Excel.Range;
        printDateValue.Value2 = DateTime.Now;
        setDateTimeFormat(printDateValue, contextFontSize, columnWidth);
        // set print person
        Excel.Range printPerson = worksheet.Cells[2, 1] as Excel.Range;
        printPerson.Value2 = Resources.ECCResource.PrintPerson;
        setCharacterFormat(printPerson, contextFontSize, columnWidth);
        Excel.Range printPersonValue = worksheet.Cells[2, 2] as Excel.Range;
        printPersonValue.Value2 = Session[ECCConstant.SESSION_USERID].ToString();
        setCharacterFormat(printPersonValue, contextFontSize, columnWidth);
        // set query date range
        Excel.Range startDate = worksheet.Cells[3, 1] as Excel.Range;
        startDate.Value2 = Resources.ECCResource.BeginDate;
        setCharacterFormat(startDate, contextFontSize, columnWidth);
        Excel.Range startDateValue = worksheet.Cells[3, 2] as Excel.Range;
        startDateValue.Value2 = this.DtbDateBegin.Value;
        setCharacterFormat(startDateValue, contextFontSize, columnWidth);
        Excel.Range endDate = worksheet.Cells[3, 3] as Excel.Range;
        endDate.Value2 = Resources.ECCResource.EndDate;
        setCharacterFormat(endDate, contextFontSize, columnWidth);
        Excel.Range endDateValue = worksheet.Cells[3, 4] as Excel.Range;
        endDateValue.Value2 = this.DtbDateEnd.Value;
        setCharacterFormat(endDateValue, contextFontSize, columnWidth);        // set title
        Excel.Range titleRange = worksheet.get_Range(
            worksheet.Cells[5, 1], worksheet.Cells[5, colCount - 1]);   // select the cell
        titleRange.Merge(true);                                         // merge the cell
        //TBD
        titleRange.Value2 = title;                                      // set text
        titleRange.Font.Name = "宋体";                                  // set the name of the font 
        titleRange.Font.Size = titleFontSize;                           // set  the size of the font
        titleRange.Font.Bold = true;                                    // bold
        titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // horizontal center
        titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;   // vertical
        titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;  // set border
        titleRange.Borders.Weight = Excel.XlBorderWeight.xlThin;        // set border thickness        // set table's head
        for (int i = 1; i < colCount; i++)
        {
            Excel.Range range = worksheet.Cells[6, i] as Excel.Range;       // get header cell
            range.Interior.ColorIndex = 16;                                 // fill color
            range.Value2 = gridView.Columns[i].HeaderText;                  // set cell's text
            range.Font.Name = "宋体";                                       // set font
            range.Font.ColorIndex = 2;
            range.Font.Size = contextFontSize;                              // set font size
            range.Font.Bold = true;                                         // font bond
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;      // horizontal center
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;        // vertical center
            range.ColumnWidth = columnWidth;                                // set border width
            range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;       // set border
            range.Borders.Weight = Excel.XlBorderWeight.xlThin;             // set border thickness
        }        // set context
        for (int i = 0; i < rowCount; i++)
        {
            for (int j = 1; j < colCount; j++)
            {
                Excel.Range range = worksheet.Cells[7 + i, j] as Excel.Range;   // get context cell
                range.Value2 = gridView.Rows[i].Cells[j].Text;                  // set cell' text
                range.Font.Size = contextFontSize;                              // set size of character  
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;      // horizontal center
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;        // vertical center
                range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;       // set border
                range.Borders.Weight = Excel.XlBorderWeight.xlThin;             // set border thickness
                range.ColumnWidth = columnWidth;                                // set border width                // Set percentage format
                if (gridView.Rows[i].Cells[j].Text.EndsWith("%"))
                {
                    range.NumberFormatLocal = "0.0%";
                }                if (i == gridView.Rows.Count - 1 && lastLineHighligt)
                {
                    range.Interior.ColorIndex = 2;                                      // set font color
                    range.Font.Name = "宋体";                                     // set font
                    range.Font.Bold = true;                                              // bond font
                }// set the font to the number
                else
                {
                    range.Font.Name = "宋体";
                }
            }
        }
    }

Answer 2

例程参考：http://blog.csdn.net/gisfarmer/archive/2009/01/09/3738959.aspx

Answer 3

非常感谢各位大虾 我引入了excel的dll文件，在内存中生成一个excel导出的 
小妹偶菜鸟一只，请问合并单元格是以下哪句代码呢？// set context 
        for (int i = 0; i < rowCount; i++) 
        { 
            for (int j = 1; j < colCount; j++) 
            { 
                Excel.Range range = worksheet.Cells[7 + i, j] as Excel.Range;  // get context cell 
                range.Value2 = gridView.Rows[i].Cells[j].Text;                  // set cell' text 
                range.Font.Size = contextFontSize;                              // set size of character  
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;      // horizontal center 
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;        // vertical center 
                range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;      // set border 
                range.Borders.Weight = Excel.XlBorderWeight.xlThin;            // set border thickness 
                range.ColumnWidth = columnWidth;                                // set border width                 // Set percentage format 
                if (gridView.Rows[i].Cells[j].Text.EndsWith("%")) 
                { 
                    range.NumberFormatLocal = "0.0%"; 
                }                 if (i == gridView.Rows.Count - 1 && lastLineHighligt) 
                { 
                    range.Interior.ColorIndex = 2;                                      // set font color 
                    range.Font.Name = "宋体";                                    // set font 
                    range.Font.Bold = true;                                              // bond font 
                }// set the font to the number 
                else 
                { 
                    range.Font.Name = "宋体"; 
                } 
            } 

Answer 4

可以看这里。有实例下载。
C#导出Excel合并单元格

Answer 5

可以看这里有实例下载。C#导出Excel合并单元格