通过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