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 保存 }