public static void Main()
{
// Load sample Excel file containing cells with formatting.
Workbook wb = new Workbook(sourceDir + "sampleChangeCellsAlignmentAndKeepExistingFormatting.xlsx");
// Access first worksheet.
Worksheet ws = wb.Worksheets[0];
// Create cells range.
Range rng = ws.Cells.CreateRange("B2:D7");
// Create style object.
Style st = wb.CreateStyle();
// Set the horizontal and vertical alignment to center.
st.HorizontalAlignment = TextAlignmentType.Center;
st.VerticalAlignment = TextAlignmentType.Center;
// Create style flag object.
StyleFlag flag = new StyleFlag();
// Set style flag alignments true. It is most crucial statement.
// Because if it will be false, no changes will take place.
flag.Alignments = true;
// Apply style to range of cells.
rng.ApplyStyle(st, flag);
// Save the workbook in XLSX format.
wb.Save(outputDir + "outputChangeCellsAlignmentAndKeepExistingFormatting.xlsx", SaveFormat.Xlsx);
Console.WriteLine("ChangeCellsAlignmentAndKeepExistingFormatting executed successfully.");
}
/// <summary>
/// 标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="pRange"></param>
private static void DrawTitleStyle(Workbook workbook, Range pRange)
{
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
style.ForegroundColor = c;
style.Pattern = BackgroundType.Solid;
style.Font.Color = Color.White;
style.Font.IsBold = true;
//s.Borders.DiagonalColor = Color.Red;
//s.Borders.DiagonalStyle= CellBorderType.Thick;
//s.Borders.SetColor(Color.Red);
//s.Borders.SetStyle(CellBorderType.Thick);
//s.SetBorder(BorderType.Horizontal | BorderType.Vertical, CellBorderType.Dashed , Color.Red);
//s.SetBorder(BorderType.LeftBorder | BorderType.RightBorder | BorderType.BottomBorder | BorderType.TopBorder, CellBorderType.Thick, Color.Red);
style.HorizontalAlignment = TextAlignmentType.Center;
//s.ShrinkToFit = true;
SetBorder(style);
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a new Workbook.
Workbook wb1 = new Workbook();
//Get the first worksheet in the workbook.
Worksheet worksheet1 = wb1.Worksheets[0];
//Create a range.
Range mrange = worksheet1.Cells.CreateRange("A18", "J18");
//Name the range.
mrange.Name = "Details";
//Merge the cells of the range.
mrange.Merge();
//Get the range.
Range range1 = wb1.Worksheets.GetRangeByName("Details");
//Add a style object to the collection.
int i = wb1.Styles.Add();
//Define a style object.
Style style = wb1.Styles[i];
//Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center;
//Create a StyleFlag object.
StyleFlag flag = new StyleFlag();
//Make the relative style attribute ON.
flag.HorizontalAlignment = true;
//Apply the style to the range.
range1.ApplyStyle(style, flag);
//Input data into range.
range1[0, 0].PutValue("Aspose");
//Save the excel file.
wb1.Save(dataDir + "mergingrange.out.xls");
//ExEnd:1
}
public static Range SetFontUnderline(this Range range, FontUnderlineType value)
{
var style = new Style();
style.Font.Underline = value;
range.ApplyStyle(style, new StyleFlag {
FontUnderline = true
});
return(range);
}
public static Range SetFontStrikeout(this Range range, bool value = true)
{
var style = new Style();
style.Font.IsStrikeout = value;
range.ApplyStyle(style, new StyleFlag {
FontStrike = true
});
return(range);
}
public static Range SetFontName(this Range range, string value = Settings.FontName)
{
var style = new Style();
style.Font.Name = value;
range.ApplyStyle(style, new StyleFlag {
FontName = true
});
return(range);
}
public static Range SetFontColor(this Range range, Color value)
{
var style = new Style();
style.Font.Color = value;
range.ApplyStyle(style, new StyleFlag {
FontColor = true
});
return(range);
}
public static Range SetVerticalAlignment(this Range range, TextAlignmentType value)
{
var style = new Style();
style.VerticalAlignment = value;
range.ApplyStyle(style, new StyleFlag {
VerticalAlignment = true
});
return(range);
}
public static Range SetTextWrapped(this Range range, bool value = true)
{
var style = new Style();
style.IsTextWrapped = value;
range.ApplyStyle(style, new StyleFlag {
WrapText = true
});
return(range);
}
public static Range SetFormat(this Range range, string value, params object[] args)
{
var style = new Style();
style.Custom = string.Format(value, args);
range.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(range);
}
public static Range SetFormat(this Range range, int value)
{
var style = new Style();
style.Number = value;
range.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(range);
}
public static Range SetFontSize(this Range range, int value = Settings.FontSize)
{
var style = new Style();
style.Font.Size = value;
range.ApplyStyle(style, new StyleFlag {
FontSize = true
});
return(range);
}
/// <summary>
/// 设置标题区域的样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="pRange"></param>
private void SetTitleStyle(Workbook workbook, Range pRange)
{
Style style = this.CreateStyle(workbook);
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
/// <summary>
/// 设置摘要区域的样式
/// </summary>
/// <param name="pWorkbook"></param>
/// <param name="pRange"></param>
private void SetSummaryRangeStyle(Workbook pWorkbook, Range pRange)
{
Style dataRangeStyle = pWorkbook.Styles[pWorkbook.Styles.Add()];
SetBorder(dataRangeStyle);
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(dataRangeStyle, styleFlag);
}
public static Range SetForegroundColor(this Range range, System.Drawing.Color value)
{
var style = new Style();
style.ForegroundColor = value;
style.Pattern = BackgroundType.Solid;
range.ApplyStyle(style, new StyleFlag {
CellShading = true
});
return(range);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range of cells.
Range range = cells.CreateRange("A6", "P216");
// Declare style.
Style stl;
// Create the style adding to the style collection.
stl = workbook.CreateStyle();
// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
/// <summary>
/// 设置面包屑导航的样式
/// </summary>
/// <param name="pWorkbook"></param>
/// <param name="pRange"></param>
private void SetNavigationStyle(Workbook pWorkbook, Range pRange)
{
Style style = this.CreateStyle(pWorkbook);
style.HorizontalAlignment = TextAlignmentType.Left;
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
/// <summary>
/// 边框样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawBorderAll(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Black;
s.Borders.SetColor(c);
s.Borders.SetStyle(CellBorderType.Thin);
s.Borders.DiagonalStyle = CellBorderType.None;
s.Pattern = BackgroundType.Solid;
StyleFlag styleFlag = new StyleFlag();
styleFlag.Borders = true;
r.ApplyStyle(s, styleFlag);
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create a workbook.
Workbook workbook = new Workbook();
// Create a new style object.
Style style = workbook.CreateStyle();
// Set the number format.
style.Number = 14;
// Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.Color.Yellow;
// Name the style.
style.Name = "MyCustomDate";
// Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
// Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
// Create a range (B1:D1).
Range range = cells.CreateRange("B6", "D10");
// Initialize styleflag object.
StyleFlag flag = new StyleFlag();
// Set all formatting attributes on.
flag.All = true;
Style style2 = workbook.GetNamedStyle("MyCustomDate");
// Apply the style (described above)to the range.
range.ApplyStyle(style2, flag);
cells["C8"].PutValue(43105);
// Save the excel file.
workbook.Save(outputDir + "outputModifyThroughStyleObject.xlsx");
Console.WriteLine("ModifyThroughStyleObject executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a workbook.
Workbook workbook = new Workbook();
// Create a new style object.
Style style = workbook.CreateStyle();
// Set the number format.
style.Number = 14;
// Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
// Name the style.
style.Name = "Date1";
// Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
// Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
// Create a range (B1:D1).
Range range = cells.CreateRange("B1", "D1");
// Initialize styleflag object.
StyleFlag flag = new StyleFlag();
// Set all formatting attributes on.
flag.All = true;
// Apply the style (described above)to the range.
range.ApplyStyle(style, flag);
// Modify the style (described above) and change the font color from red to black.
style.Font.Color = System.Drawing.Color.Black;
// Done! Since the named style (described above) has been set to a cell and range,
// The change would be Reflected(new modification is implemented) to cell(A1) and // Range (B1:D1).
style.Update();
// Save the excel file.
workbook.Save(dataDir + "book_styles.out.xls");
// ExEnd:1
}
/// <summary>
/// 必填字段的样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawMastFillStyle(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Maroon;
s.ForegroundColor = c;
s.Pattern = BackgroundType.Solid;
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
r.ApplyStyle(s, styleFlag);
}
public static void Run()
{
// Instantiate a new Workbook.
Workbook wb1 = new Workbook();
// Get the first worksheet in the workbook.
Worksheet worksheet1 = wb1.Worksheets[0];
// Create a range.
Range mrange = worksheet1.Cells.CreateRange("D6", "I12");
// Name the range.
mrange.Name = "TestRange";
// Merge the cells of the range.
mrange.Merge();
// Get the range.
Range range1 = wb1.Worksheets.GetRangeByName("TestRange");
// Define a style object.
Style style = wb1.CreateStyle();
// Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.Color.Aqua;
// Create a StyleFlag object.
StyleFlag flag = new StyleFlag();
// Make the relative style attribute ON.
flag.HorizontalAlignment = true;
flag.VerticalAlignment = true;
flag.CellShading = true;
// Apply the style to the range.
range1.ApplyStyle(style, flag);
// Input data into range.
range1[0, 0].PutValue("Welcome to Aspose APIs.");
// Save the excel file.
wb1.Save(outputDir + "outputMergeCellsInNamedRange.xlsx");
Console.WriteLine("MergeCellsInNamedRange executed successfully.");
}
public static void Run()
{
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range of cells.
Range range = cells.CreateRange("D6", "M16");
// Declare style.
Style stl;
// Create the style adding to the style collection.
stl = workbook.CreateStyle();
// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(outputDir + "outputSetBorderAroundEachCell.xlsx");
Console.WriteLine("SetBorderAroundEachCell executed successfully.");
}
/// <summary>
/// 标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawTitleStyle(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
s.ForegroundColor = c;
s.Pattern = BackgroundType.Solid;
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
r.ApplyStyle(s, styleFlag);
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate a workbook object.
//Open an existing excel file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
//Check whether the first range intersect the second range.
bool isintersect = ranges[0].IsIntersect(ranges[1]);
//Create a style object.
Style style = workbook.Styles[workbook.Styles.Add()];
//Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
//Create a styleflag object.
StyleFlag flag = new StyleFlag();
//Apply the cellshading.
flag.CellShading = true;
//If first range intersects second range.
if (isintersect)
{
//Create a range by getting the intersection.
Range intersection = ranges[0].Intersect(ranges[1]);
//Name the range.
intersection.Name = "Intersection";
//Apply the style to the range.
intersection.ApplyStyle(style, flag);
}
//Save the excel file.
workbook.Save(dataDir + "rngIntersection.out.xls");
//ExEnd:1
}
public static void Run()
{
// Instantiate a workbook object.
// Open an existing excel file.
Workbook workbook = new Workbook(sourceDir + "sampleIntersectionOfRanges.xlsx");
// Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
// Check whether the first range intersect the second range.
bool isintersect = ranges[0].IsIntersect(ranges[1]);
// Create a style object.
Style style = workbook.CreateStyle();
// Set the shading color with solid pattern type.
style.ForegroundColor = Color.Red;
style.Pattern = BackgroundType.Solid;
// Create a styleflag object.
StyleFlag flag = new StyleFlag();
// Apply the cellshading.
flag.CellShading = true;
// If first range intersects second range.
if (isintersect)
{
// Create a range by getting the intersection.
Range intersection = ranges[0].Intersect(ranges[1]);
// Name the range.
intersection.Name = "Intersection";
// Apply the style to the range.
intersection.ApplyStyle(style, flag);
}
// Save the excel file.
workbook.Save(outputDir + "outputIntersectionOfRanges.xlsx");
Console.WriteLine("IntersectionOfRanges executed successfully.");
}
public static void Main()
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
//Fill some sample data into the cells.
for (int i = 0; i < 50; i++)
{
for (int j = 0; j < 10; j++)
{
cells[i, j].PutValue(i.ToString() + "," + j.ToString());
}
}
//Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");
//Create a style object.
Style style;
style = workbook.Styles[workbook.Styles.Add()];
//Specify the font attribute.
style.Font.Name = "Calibri";
//Specify the shading color.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
//Specify the border attributes.
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Blue;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Blue;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Blue;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Blue;
//Create the styleflag object.
StyleFlag flag1 = new StyleFlag();
//Implement font attribute
flag1.FontName = true;
//Implement the shading / fill color.
flag1.CellShading = true;
//Implment border attributes.
flag1.Borders = true;
//Set the Range style.
range.ApplyStyle(style, flag1);
//Create a second range (C10:E13).
Range range2 = cells.CreateRange("C10", "E13");
//Copy the range style only.
range2.CopyStyle(range);
//Save the excel file.
workbook.Save(dataDir + "copyrangestyle.out.xls");
//ExEnd:1
}
public Workbook GetAllBooksExcel()
{
//Instantiate License class and call its SetLicense method to use the license
string licPath = @"C:\ProjectBookClub\Aspose.Total.lic";
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
DataSet dataset = new DataSet();
string bkConn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["BookClubConnString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(bkConn))
{
SqlCommand cmd = new SqlCommand("_sp_GetAllBooksExcel", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
//DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataset);
}
DataTable books = dataset.Tables[0];
Workbook workBook = new Workbook();
workBook.Worksheets.Clear();
// Format the header row
Worksheet workSheet = workBook.Worksheets.Add("Books");
workSheet.Cells.ImportDataTable(books, true, "A1");
Aspose.Cells.Range range = workSheet.Cells.CreateRange("A1", "I1");
Aspose.Cells.Style headerStyle = workBook.CreateStyle();
headerStyle.Font.Name = "Calibri";
headerStyle.Font.IsBold = true;
headerStyle.Font.Size = 13;
StyleFlag flg = new StyleFlag();
flg.Font = true;
range.ApplyStyle(headerStyle, flg);
//Format font for the rest of the sheet
Range range1 = workSheet.Cells.CreateRange("A2", "I100");
Style style1 = workBook.CreateStyle();
style1.Font.Name = "Calibri";
style1.Font.Size = 12;
StyleFlag flg1 = new StyleFlag();
flg1.Font = true;
range1.ApplyStyle(style1, flg);
// Title
workSheet.Cells.SetColumnWidth(0, 40);
// Author
workSheet.Cells.SetColumnWidth(1, 25);
// Date Read - set date format for this column
Aspose.Cells.Style style = workBook.CreateStyle();
style.Number = 14;
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
//workSheet.Cells.SetColumnWidth(7, 16);
//workSheet.Cells.Columns[7].ApplyStyle(style, flag);
workSheet.Cells.SetColumnWidth(2, 12);
workSheet.Cells.Columns[2].ApplyStyle(style, flag);
// Main Characters
workSheet.Cells.SetColumnWidth(3, 40);
// Notes
workSheet.Cells.SetColumnWidth(4, 40);
// Book Club
workSheet.Cells.SetColumnWidth(5, 20);
// Book Information
workSheet.Cells.SetColumnWidth(6, 40);
// Stars
workSheet.Cells.SetColumnWidth(7, 15);
// Genre
workSheet.Cells.SetColumnWidth(8, 15);
return(workBook);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
//Fill some sample data into the cells.
for (int i = 0; i < 50; i++)
{
for (int j = 0; j < 10; j++)
{
cells[i, j].PutValue(i.ToString() + "," + j.ToString());
}
}
//Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");
//Create a style object.
Style style;
style = workbook.Styles[workbook.Styles.Add()];
//Specify the font attribute.
style.Font.Name = "Calibri";
//Specify the shading color.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
//Specify the border attributes.
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Blue;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Blue;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Blue;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Blue;
//Create the styleflag object.
StyleFlag flag1 = new StyleFlag();
//Implement font attribute
flag1.FontName = true;
//Implement the shading / fill color.
flag1.CellShading = true;
//Implment border attributes.
flag1.Borders = true;
//Set the Range style.
range.ApplyStyle(style, flag1);
//Create a second range (C10:F12).
Range range2 = cells.CreateRange("C10", "F12");
//Copy the range data only.
range2.CopyData(range);
//Save the excel file.
workbook.Save(dataDir + "CopyRangeData.xlsx");
}
/// <summary>
/// 数据导出excel
/// </summary>
/// <param name="paramClass">导出参数</param>
public static void OutPutExcel(OutPutParamClass paramClass)
{
StringBuilder str = new StringBuilder();
#region 合法性检测
if (null == paramClass.Ds || paramClass.Ds.Tables.Count == 0)
{
str.Append("要导出的数据不能为空，导出失败！；");
}
if (null != paramClass.ConTitle && null != paramClass.Ds)
{
if (paramClass.ConTitle.Length != paramClass.Ds.Tables.Count)
{
str.Append("Sheet的名称信息与要导出的数据源的数量不匹配，每一个Sheet必须要有一个名称，导出失败！；");
}
if (paramClass.ConTitle.Distinct().Count() != paramClass.ConTitle.Length)
{
str.Append("Sheet的名称信息不能重复，导出失败！；");
}
}
if (null != paramClass.OutPutClass && paramClass.OutPutClass.Count > 0)
{
if (paramClass.TableName.Length != paramClass.Ds.Tables.Count)
{
str.Append("表名与dataSet的table数量不一致，导出失败！；");
}
}
if (str.Length > 0)
{
throw new ArgumentException(str.ToString(), "paramClass");
}
#endregion 合法性检测
#region 是否指定被操作的工作薄
Workbook workbook = null;
if (!string.IsNullOrEmpty(paramClass.WorkBookFilePath))
{
workbook = new Workbook(paramClass.WorkBookFilePath);
}
else
{
workbook = new Workbook();
}
#endregion 是否指定被操作的工作薄
for (int i = 0; i < paramClass.Ds.Tables.Count; i++)
{
Worksheet sheet = workbook.Worksheets[i];
if (null != paramClass.ConTitle && paramClass.ConTitle.Length > 0)
{
sheet.Name = paramClass.ConTitle[i];
}
if (i != paramClass.Ds.Tables.Count - 1)
{
workbook.Worksheets.Add();
}
DataTable dt = paramClass.Ds.Tables[i];
List <string> dtColNameLst = new List <string>();
for (int k = 0; k < dt.Columns.Count; k++)
{
dtColNameLst.Add(dt.Columns[k].ColumnName);
}
#region 写入列名
List <string> newNamesLst = new List <string>();
if (null != paramClass.OutPutClass && paramClass.OutPutClass.Count > 0)
{
OutPutClass outPutModel = paramClass.OutPutClass.First(k => k.TableName == paramClass.TableName[i].Trim());
foreach (var m in outPutModel.Fields)
{
newNamesLst.Add(m.newName);
if (dtColNameLst.Contains(m.oldName))
{
dt.Columns[m.oldName].ColumnName = m.newName;
}
}
}
else
{
newNamesLst = dtColNameLst;
}
#endregion 写入列名
#region 向sheet中写入数据
dt.AcceptChanges();
sheet.Cells.ImportDataTable(dt.DefaultView.ToTable("dtNew", true, newNamesLst.ToArray()), paramClass.IsShowFieldLine, paramClass.FirstRowIndex, paramClass.FirstColumnIndex, dt.Rows.Count + 1, dt.Columns.Count, true, "yyyy-MM-dd HH:mm:ss");
sheet.AutoFitColumns();
#endregion 向sheet中写入数据
#region 添加样式
Cells cells = sheet.Cells;
if (paramClass.IsShowCustomLine)
{
cells[0, 0].Value = string.Format("数据导出：{0}；导出时间：{1}；记录总数：{2}", paramClass.ConTitle[i], DateTime.Now, dt.Rows.Count);
Aspose.Cells.Style styleCell0 = cells[0, 0].GetStyle();
styleCell0.Font.Color = System.Drawing.Color.Red;
cells[0, 0].SetStyle(styleCell0);
}
if (paramClass.IsShowFieldLine)
{
Range range = sheet.Cells.CreateRange(1, 0, 1, newNamesLst.Count);
range.Name = "Range1";
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = System.Drawing.Color.Blue;
style.Font.IsBold = true;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
range.ApplyStyle(style, styleFlag);
}
#endregion 添加样式
}
paramClass.GetWorkBook = workbook;
#region 保存
if (!string.IsNullOrEmpty(paramClass.CustomFileName))
{
workbook.Save(paramClass.CustomFileName, paramClass.SaveFormat);
}
if (paramClass.AutoDownLoad)
{
string fileName = string.Format("{0}_数据导出.xlsx", paramClass.FileTitle);
bool isFirefox = HttpContext.Current.Request.Browser.Type.ToLower().Contains("firefox");
fileName = isFirefox ? fileName : HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
workbook.Save(HttpContext.Current.Response, fileName, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
#endregion 保存
}
