Devexpress Spreadsheet 中文教程（3）

导入和导出数据

如何导入数据到Worksheet

Import Data from Arrays

Worksheet worksheet = workbook.W

orksheets[0];
// Create an array containing string values.
string[] array = new string[] { "AAA", "BBB", "CCC", "DDD" };
// Import the array into the worksheet and insert it horizontally, starting with the B1 cell.
worksheet.Import(array, 0, 1, false);
// Create the two-dimensional array containing string values.
String[,] names = new String[2, 4]{
{"Ann", "Edward", "Angela", "Alex"},
{"Rachel", "Bruce", "Barbara", "George"}
};
// Import a two-dimensional array into the worksheet and insert it, starting with the B3 cell.
worksheet.Import(names, 2, 1);

Import Data from a List

Worksheet worksheet = workbook.Worksheets[0];
// Create the List object containing string values.
List<string> cities = new List<string>();
cities.Add("New York");
cities.Add("Rome");
cities.Add("Beijing");
cities.Add("Delhi");
// Import the list into the worksheet and insert it vertically, starting worksheet.Import(cities, 0, 0, true);

Import Data from a DataTable

Worksheet worksheet = workbook.Worksheets[0];
// Create the "Employees" DataTable object with four columns.
DataTable table = new DataTable("Employees");
table.Columns.Add("FirstN", typeof(string));
table.Columns.Add("LastN", typeof(string));
table.Columns.Add("JobTitle", typeof(string));
table.Columns.Add("Age", typeof(Int32));
table.Rows.Add("Nancy", "Davolio", "recruiter", 32);
table.Rows.Add("Andrew", "Fuller", "engineer", 28);
// Import data from the data table into the worksheet and insert it, starting worksheet.Import(table, true, 0, 0);
// Color the table header.
for (int i = 1; i < 5; i++) {
worksheet.Cells[10, i].FillColor = Color.LightGray;

为单元格或单元格区域应用样式

Worksheet worksheet = workbook.Worksheets[0];
// Access the built-in "Good" MS Excel style from the Styles collection of the workbook.
Style styleGood = workbook.Styles[BuiltInStyleId.Good];
// Apply the "Good" style to a range of cells.
worksheet.Range["A1:C4"].Style = styleGood;
// Access a custom style that has been previously created in the loaded document by its name.
Style customStyle = workbook.Styles["Custom Style"];
// Apply the custom style to the cell.
worksheet.Cells["D6"].Style = customStyle;
// Apply the "Good" style to the eighth row.
worksheet.Rows[7].Style = styleGood;
// Apply the custom style to the "H" column.
worksheet.Columns["H"].Style = customStyle;

创建或修改样式

// Add a new style under the "My Style" name to the Styles collection of the workbook.
Style myStyle = workbook.Styles.Add("My Style");
// Specify formatting characteristics for the style.
myStyle.BeginUpdate();
try {
// Set the font color to Blue.
myStyle.Font.Color = Color.Blue;
// Set the font size to 12.
myStyle.Font.Size = 12;
// Set the horizontal alignment to Center.
myStyle.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
// Set the background.
myStyle.Fill.BackgroundColor = Color.LightBlue;
myStyle.Fill.PatternType = PatternType.LightGray;
myStyle.Fill.PatternColor = Color.Yellow;
}
finally {
myStyle.EndUpdate();
}

通过内建样式新建样式

<pre class="csharp" name="code">// Add a new style under the "My Good Style" name to the Styles collection.
Style myGoodStyle = workbook.Styles.Add("My Good Style");
// Copy all format settings from the built-in Good style.
myGoodStyle.CopyFrom(BuiltInStyleId.Good);
// Modify the required formatting characteristics if needed.
// ...

修改现有样式

// Access the style to be modified.
Style customStyle = workbook.Styles["Custom Style"];
// Change the required formatting characteristics of the style.
customStyle.BeginUpdate();
try {
customStyle.Fill.BackgroundColor = Color.Gold;
// ...
} finally {
customStyle.EndUpdate();
}

// Modify the style applied to the "F10" cell.
workbook.Worksheets[0].Cells["F10"].Style.Fill.BackgroundColor = Color.// Modify the style applied to the "K8:M11" cell range.
workbook.Worksheets[0].CreateRange("K8:M11").Style.Font.Color = Color

格式化单元格或者单元格区域

// Access the cell to be formatted.
Cell cell = worksheet.Cells["B2"];
// Specify font settings (font name, color, size and style).
cell.Font.Name = "MV Boli";
cell.Font.Color = Color.Blue;
cell.Font.Size = 14;
cell.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
cell.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in the cell.
cell.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
cell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

// Access the range of cells to be formatted.
Range range = worksheet.Range["C3:E6"];
// Begin updating of the range formatting.
Formatting rangeFormatting = range.BeginUpdateFormatting();
// Specify font settings (font name, color, size and style).
rangeFormatting.Font.Name = "MV Boli";
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Font.Size = 14;
rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
rangeFormatting.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in cells.
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
// End updating of the range formatting.
range.EndUpdateFormatting(rangeFormatting);

指定单元格内容的数字或日期格式

日期时间格式

worksheet.Range["A1:F1"].Formula = "= Now()";
// Apply different date display formats.
worksheet.Cells["A1"].NumberFormat = "m/d/yy";
worksheet.Cells["B1"].NumberFormat = "d-mmm-yy";
worksheet.Cells["C1"].NumberFormat = "dddd";
// Apply different time display formats.
worksheet.Cells["D1"].NumberFormat = "m/d/yy h:mm";
worksheet.Cells["E1"].NumberFormat = "h:mm AM/PM";
worksheet.Cells["F1"].NumberFormat = "h:mm:ss";

数字格式

// Display 111 as 111.
worksheet.Cells["A1"].Value = 111;
worksheet.Cells["A1"].NumberFormat = "#####";
// Display 222 as 00222.
worksheet.Cells["B1"].Value = 222;
worksheet.Cells["B1"].NumberFormat = "00000";
// Display 12345678 as 12,345,678.
worksheet.Cells["C1"].Value = 12345678;
worksheet.Cells["C1"].NumberFormat = "#,#";
// Display .126 as 0.13.
worksheet.Cells["D1"].Value = .126;
worksheet.Cells["D1"].NumberFormat = "0.##";
// Display 74.4 as 74.400.
worksheet.Cells["E1"].Value = 74.4;
worksheet.Cells["E1"].NumberFormat = "##.000";
// Display 1.6 as 160.0%.
worksheet.Cells["F1"].Value = 1.6;
worksheet.Cells["F1"].NumberFormat = "0.0%";
// Display 4321 as $4,321.00.
worksheet.Cells["G1"].Value = 4321;
worksheet.Cells["G1"].NumberFormat = "$#,##0.00";
// Display 8.75 as 8 3/4.
worksheet.Cells["H1"].Value = 8.75;
worksheet.Cells["H1"].NumberFormat = "# ?/?";

自定义数字格式

// Set cell values.
worksheet["A2:B2"].Value = -15.50;
worksheet["A3:B3"].Value = 555;
worksheet["A4:B4"].Value = 0;
worksheet["A5:B5"].Value = "Name";
//Apply custom number format.
worksheet["B2:B5"].NumberFormat = "[Green]#.00;[Red]#.00;[Blue]0.00;[Cyan]

修改字体以及背景颜色

// Format an individual cell.
worksheet.Cells["A1"].Font.Color = Color.Red;
worksheet.Cells["A1"].FillColor = Color.Yellow;
// Format a range of cells.
Range range = worksheet.Range["C3:D4"];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Fill.BackgroundColor = Color.LightBlue;
rangeFormatting.Fill.PatternType = PatternType.LightHorizontal;
rangeFormatting.Fill.PatternColor = Color.Violet;
range.EndUpdateFormatting(rangeFormatting);

配置单元格字体格式

// Access the Font object.
SpreadsheetFont cellFont = worksheet.Cells["A1"].Font;
// Set the font name.
cellFont.Name = "Times New Roman";
// Set the font size.
cellFont.Size = 14;
// Set the font color.
cellFont.Color = Color.Blue;
// Format text as bold.
cellFont.Bold = true;
// Set font to be underlined.
cellFont.UnderlineType = UnderlineType.Double;

单元格内容对齐

Cell cellA1 = worksheet.Cells["A1"];
cellA1.Value = "Right and top";
cellA1.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Right;
cellA1.Alignment.Vertical = SpreadsheetVerticalAlignment.Top;
Cell cellA2 = worksheet.Cells["A2"];
cellA2.Value = "Center";
cellA2.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
cellA2.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Cell cellA3 = worksheet.Cells["A3"];
cellA3.Value = "Left and bottom, indent";
cellA3.Alignment.Indent = 1;
Cell cellB1 = worksheet.Cells["B1"];
cellB1.Value = "The Alignment.ShrinkToFit property is applied";
cellB1.Alignment.ShrinkToFit = true;
Cell cellB2 = worksheet.Cells["B2"];
cellB2.Value = "Rotated Cell Contents";
cellB2.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
cellB2.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
cellB2.Alignment.RotationAngle = 15;
Cell cellB3 = worksheet.Cells["B3"];
cellB3.Value = "The Alignment.WrapText property is applied to wrap the text within a cell";
cellB3.Alignment.WrapText = true;

添加删除单元格边框

Worksheet worksheet = workbook.Worksheets[0];
// Set each particular border for the cell.
Cell cellB2 = worksheet.Cells["B2"];
Borders cellB2Borders = cellB2.Borders;
cellB2Borders.LeftBorder.LineStyle = BorderLineStyle.MediumDashDot;
cellB2Borders.LeftBorder.Color = Color.Pink;
cellB2Borders.TopBorder.LineStyle = BorderLineStyle.MediumDashDotDot;
cellB2Borders.TopBorder.Color = Color.HotPink;
cellB2Borders.RightBorder.LineStyle = BorderLineStyle.MediumDashed;
cellB2Borders.RightBorder.Color = Color.DeepPink;
cellB2Borders.BottomBorder.LineStyle = BorderLineStyle.Medium;
cellB2Borders.BottomBorder.Color = Color.Red;
cellB2Borders.DiagonalBorderType = DiagonalBorderType.Up;
cellB2Borders.DiagonalBorderLineStyle = BorderLineStyle.Thick;
cellB2Borders.DiagonalBorderColor = Color.Red;

// Set diagonal borders for the cell.
Cell cellC4 = worksheet.Cells["C4"];
Borders cellC4Borders = cellC4.Borders;
cellC4Borders.SetDiagonalBorders(Color.Orange, BorderLineStyle.Double, DiagonalBorderType.UpAndDown);
// Set all outside borders for the cell in one step.
Cell cellD6 = worksheet.Cells["D6"];
cellD6.Borders.SetOutsideBorders(Color.Gold, BorderLineStyle.Double);
// Set all borders for the range of cells in one step.
Range range1 = worksheet.Range["B8:F13"];
range1.Borders.SetAllBorders(Color.Green, BorderLineStyle.Double);
// Set all inside and outside borders separately for the range of cells.
Range range2 = worksheet.Range["C15:F18"];
range2.SetInsideBorders(Color.SkyBlue, BorderLineStyle.MediumDashed);
range2.Borders.SetOutsideBorders(Color.DeepSkyBlue, BorderLineStyle.Medium);
// Set all horizontal and vertical borders separately for the range of cells.
Range range3 = worksheet.Range["D21:F23"];
Formatting range3Formatting = range3.BeginUpdateFormatting();
Borders range3Borders = range3Formatting.Borders;
range3Borders.InsideHorizontalBorders.LineStyle = BorderLineStyle.MediumDashDot;
range3Borders.InsideHorizontalBorders.Color = Color.DarkBlue;
range3Borders.InsideVerticalBorders.LineStyle = BorderLineStyle.MediumDashDotDot;
range3Borders.InsideVerticalBorders.Color = Color.Blue;
range3.EndUpdateFormatting(range3Formatting);
// Set each particular border for the range of cell.
Range range4 = worksheet.Range["E25:F26"];
Formatting range4Formatting = range4.BeginUpdateFormatting();
Borders range4Borders = range4Formatting.Borders;
range4Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thick);
range4Borders.LeftBorder.Color = Color.Violet;
range4Borders.TopBorder.Color = Color.Violet;
range4Borders.RightBorder.Color = Color.DarkViolet;
range4Borders.BottomBorder.Color = Color.DarkViolet;
range4Borders.DiagonalBorderType = DiagonalBorderType.UpAndDown;
range4Borders.DiagonalBorderLineStyle = BorderLineStyle.MediumDashed;
range4Borders.DiagonalBorderColor = Color.BlueViolet;
range4.EndUpdateFormatting(range4Formatting);

清除单元格格式

using DevExpress.Spreadsheet;
// ...
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Call the ClearFormats method.
worksheet.ClearFormats(worksheet.Range["A1:C4"]);
// Apply the Normal style to cells.
worksheet.Cells["D6"].Style = workbook.Styles[0];
worksheet.Range["F3:H4"].Style = workbook.Styles[BuiltInStyleId.Normal];
worksheet.Rows[7].Style = workbook.Styles["Normal"];
worksheet.Columns["K"].Style = workbook.Styles.DefaultStyle;