通过GemBox.Spreadsheet完美操作Excel

通过GemBox.Spreadsheet操作Excel，服务端不需要安装Office完美操作Excel

1、打开Excel模板

     ExcelFile excelFile=new ExcelFile();
     var tmpath = context.Server.MapPath("/ExcelTemplate/001.xls");
     excelFile.LoadXls(tmpath);

2、合并单元格通过GetSubrangeAbsolute方法

      ExcelWorksheet sheet = excelFile.Worksheets[0];

      sheet.Cells.GetSubrangeAbsolute(0, 0, 0, columns.Count - 1).Merged = true;

 

示例代码：

 
using System; 
using System.Drawing; 
    
using GemBox.Spreadsheet; 
    
namespace Samples 
{ 
    /// <summary> 
    /// Table sample. 
    /// </summary> 
    class TableSampleCS 
    { 
        [STAThread] 
        static void Main(string[] args) 
        { 
            // TODO: If using GemBox.Spreadsheet Professional, put your serial key below. 
            // Otherwise, if you are using GemBox.Spreadsheet Free, comment out the  
            // following line (Free version doesn't have SetLicense method).  
            // SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE"); 
    
            ExcelFile excelFile = new ExcelFile(); 
    
            TypicalTableSample(excelFile.Worksheets.Add("TypicalTable")); 
    
            string fileName = "TableSample.xls"; 
            excelFile.SaveXls(fileName); 
            // Uncomment if you want to export in XLSX. 
            //string fileName = "TableSample.xlsx"; 
            //excelFile.SaveXlsx(fileName); 
    
            TryToDisplayGeneratedFile(fileName); 
        } 
    
        static object[,] skyscrapers = new object[21, 7] 
        { 
            {"Rank", "Building", "City", "Metric", "Imperial", "Floors", "Built (Year)"}, 
            { 1, "Taipei 101", "Taipei", 509, 1671, 101, 2004}, 
            { 2, "Petronas Tower 1", "Kuala Lumpur", 452, 1483, 88, 1998}, 
            { 3, "Petronas Tower 2", "Kuala Lumpur", 452, 1483, 88, 1998}, 
            { 4, "Sears Tower", "Chicago", 442, 1450, 108, 1974}, 
            { 5, "Jin Mao Tower", "Shanghai", 421, 1380, 88, 1998}, 
            { 6, "2 International Finance Centre", "Hong Kong", 415, 1362, 88, 2003}, 
            { 7, "CITIC Plaza", "Guangzhou", 391, 1283, 80, 1997}, 
            { 8, "Shun Hing Square", "Shenzhen", 384, 1260, 69, 1996}, 
            { 9, "Empire State Building", "New York City", 381, 1250, 102, 1931}, 
            {10, "Central Plaza", "Hong Kong", 374, 1227, 78, 1992}, 
            {11, "Bank of China Tower", "Hong Kong", 367, 1205, 72, 1990}, 
            {12, "Emirates Office Tower", "Dubai", 355, 1163, 54, 2000}, 
            {13, "Tuntex Sky Tower", "Kaohsiung", 348, 1140, 85, 1997}, 
            {14, "Aon Center", "Chicago", 346, 1136, 83, 1973}, 
            {15, "The Center", "Hong Kong", 346, 1135, 73, 1998}, 
            {16, "John Hancock Center", "Chicago", 344, 1127, 100, 1969}, 
            {17, "Ryugyong Hotel", "Pyongyang", 330, 1083, 105, 1992}, 
            {18, "Burj Al Arab", "Dubai", 321, 1053, 60, 1999}, 
            {19, "Chrysler Building", "New York City", 319, 1046, 77, 1930}, 
            {20, "Bank of America Plaza", "Atlanta", 312, 1023, 55, 1992} 
        }; 
    
        static void TypicalTableSample(ExcelWorksheet ws) 
        { 
            ws.Cells[0, 0].Value = "Example of typical table - tallest buildings in the world (2004):"; 
    
            // Column width of 8, 30, 16, 9, 9, 9, 9, 4 and 5 characters. 
            ws.Columns[0].Width = 8 * 256; 
            ws.Columns[1].Width = 30 * 256; 
            ws.Columns[2].Width = 16 * 256; 
            ws.Columns[3].Width = 9 * 256; 
            ws.Columns[4].Width = 9 * 256; 
            ws.Columns[5].Width = 9 * 256; 
            ws.Columns[6].Width = 9 * 256; 
            ws.Columns[7].Width = 4 * 256; 
            ws.Columns[8].Width = 5 * 256; 
    
            int i, j; 
    
            for (j = 0; j < 7; j++) 
                ws.Cells[3, j].Value = skyscrapers[0, j]; 
    
            ws.Cells.GetSubrangeAbsolute(2, 0, 3, 0).Merged = true; 
            ws.Cells.GetSubrangeAbsolute(2, 1, 3, 1).Merged = true; 
            ws.Cells.GetSubrangeAbsolute(2, 2, 3, 2).Merged = true; 
            ws.Cells.GetSubrangeAbsolute(2, 3, 2, 4).Merged = true; 
            ws.Cells[2, 3].Value = "Height"; 
            ws.Cells.GetSubrangeAbsolute(2, 5, 3, 5).Merged = true; 
            ws.Cells.GetSubrangeAbsolute(2, 6, 3, 6).Merged = true; 
    
            CellStyle tmpStyle = new CellStyle(); 
    
            tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; 
            tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; 
            tmpStyle.FillPattern.SetSolid(Color.Chocolate); 
            tmpStyle.Font.Weight = ExcelFont.BoldWeight; 
            tmpStyle.Font.Color = Color.White; 
            tmpStyle.WrapText = true; 
            tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin); 
    
            ws.Cells.GetSubrangeAbsolute(2, 0, 3, 6).Style = tmpStyle; 
    
            tmpStyle = new CellStyle(); 
    
            tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; 
            tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; 
            tmpStyle.Font.Weight = ExcelFont.BoldWeight; 
    
            CellRange mergedRange = ws.Cells.GetSubrangeAbsolute(4, 7, 13, 7); 
            mergedRange.Merged = true; 
            mergedRange.Value = "T o p   1 0"; 
            tmpStyle.Rotation = -90; 
            tmpStyle.FillPattern.SetSolid(Color.Lime); 
            mergedRange.Style = tmpStyle; 
    
            mergedRange = ws.Cells.GetSubrangeAbsolute(4, 8, 23, 8); 
            mergedRange.Merged = true; 
            mergedRange.Value = "T o p   2 0"; 
            tmpStyle.IsTextVertical = true; 
            tmpStyle.FillPattern.SetSolid(Color.Gold); 
            mergedRange.Style = tmpStyle; 
    
            mergedRange = ws.Cells.GetSubrangeAbsolute(14, 7, 23, 7); 
            mergedRange.Merged = true; 
            mergedRange.Style = tmpStyle; 
    
            for (i = 0; i < 20; i++) 
                for (j = 0; j < 7; j++) 
                { 
                    ExcelCell cell = ws.Cells[i + 4, j]; 
    
                    cell.Value = skyscrapers[i + 1, j]; 
    
                    if (i % 2 == 0) 
                        cell.Style.FillPattern.SetSolid(Color.LightSkyBlue); 
                    else
                        cell.Style.FillPattern.SetSolid(Color.FromArgb(210, 210, 230)); 
    
                    if (j == 3) 
                        cell.Style.NumberFormat = "#\" m\""; 
    
                    if (j == 4) 
                        cell.Style.NumberFormat = "#\" ft\""; 
    
                    if (j > 2) 
                        cell.Style.Font.Name = "Courier New"; 
    
                    cell.Style.Borders[IndividualBorder.Right].LineStyle = LineStyle.Thin; 
                } 
    
            ws.Cells.GetSubrange("A5", "I24").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Double); 
            ws.Cells.GetSubrange("A3", "G4").SetBorders(MultipleBorders.Vertical | MultipleBorders.Top, Color.Black, LineStyle.Double); 
            ws.Cells.GetSubrange("A5", "H14").SetBorders(MultipleBorders.Bottom | MultipleBorders.Right, Color.Black, LineStyle.Double); 
    
            ws.Cells["A27"].Value = "Notes:"; 
            ws.Cells["A28"].Value = "a) \"Metric\" and \"Imperial\" columns use custom number formatting."; 
            ws.Cells["A29"].Value = "b) All number columns use \"Courier New\" font for improved number readability."; 
            ws.Cells["A30"].Value = "c) Multiple merged ranges were used for table header and categories header."; 
        } 
    
        static void TryToDisplayGeneratedFile(string fileName) 
        { 
            try
            { 
                System.Diagnostics.Process.Start(fileName); 
            } 
            catch (Exception) 
            { 
                Console.WriteLine(fileName + " created in application folder."); 
            } 
        } 
    } 
}

 

参考网站：

http://www.gemboxsoftware.com/spreadsheet/features/samples/tablesamplecs