/// <summary> /// 公开接口函数,输出Excel /// </summary> public void Output() { #region string path = System.Web.HttpContext.Current.Server.MapPath("~"); path = path.Substring(0, path.LastIndexOf("\\")); path += @"\temp\temp.xls"; Workbook workbook = new Workbook(path); Worksheet worksheet = workbook.Worksheets[0]; this.excelCells = worksheet.Cells; this.BindDataAndStyle(); AutoFitterOptions options = new AutoFitterOptions(); options.AutoFitMergedCells = true; worksheet.AutoFitRows(options); int[] heights = new int[2] { 50, 22 }; int[] specialRow = new int[2] { 0, 1 }; for (int i = 0; i < specialRow.Length; i++) { excelCells.SetRowHeight(specialRow[i], heights[i]); } workbook.Settings.IsWriteProtected = true; workbook.Settings.WriteProtectedPassword = "******"; workbook.Save(System.Web.HttpContext.Current.Response, this.filename, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003)); #endregion }
/// <summary> /// Autofits rows for a range. /// </summary> /// <param name="range">The range.</param> /// <param name="autofitRows">Use true to autofit rows.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetAutofitRows( this Range range, bool?autofitRows) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if ((autofitRows != null) && (bool)autofitRows) { var columnNumbers = range.GetColumnNumbers(); var minColumnNumber = columnNumbers.Min(); var maxColumnNumber = columnNumbers.Max(); foreach (var rowNumber in range.GetRowNumbers()) { var rowRangeCells = range.Worksheet.GetRange(rowNumber, rowNumber, minColumnNumber, maxColumnNumber).GetCells(); if (rowRangeCells.Any(_ => _.IsMerged)) { var autoFitterOptions = new AutoFitterOptions { AutoFitMergedCells = true }; range.Worksheet.AutoFitRow(rowNumber - 1, minColumnNumber - 1, maxColumnNumber - 1, autoFitterOptions); } else { range.Worksheet.AutoFitRow(rowNumber - 1); } } } }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.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 wb = new Workbook(); // Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; // Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); // Merge the cells range.Merge(); // Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; // Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); // Set wrapping text on style.IsTextWrapped = true; // Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); // Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); // Set auto-fit for merged cells options.AutoFitMergedCells = true; // Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); dataDir = dataDir + "AutoFitMergedCells.out.xlsx"; // Save the Excel file wb.Save(dataDir); // ExEnd:1 Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir); }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.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 wb = new Workbook(); // Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; // Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); // Merge the cells range.Merge(); // Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; // Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); // Set wrapping text on style.IsTextWrapped = true; // Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); // Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); // Set auto-fit for merged cells options.AutoFitMergedCells = true; // Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); dataDir = dataDir + "AutoFitMergedCells.out.xlsx"; // Save the Excel file wb.Save(dataDir); // ExEnd:1 Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir); }
public void CellsWorkbookPostAutofitWorkbookRowsTest() { string name = "Book1.xlsx"; AutoFitterOptions autoFitterOptions = null; int? startRow = 1; int? endRow = 100; bool? onlyAuto = true; string folder = null; new Config().UpdateDataFile(folder, name); var response = instance.CellsWorkbookPostAutofitWorkbookRows(name, autoFitterOptions, startRow, endRow, onlyAuto, folder); Console.WriteLine(response); }
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 wb = new Workbook(); //Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; //Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); //Merge the cells range.Merge(); //Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; //Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); //Set wrapping text on style.IsTextWrapped = true; //Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); //Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); //Set auto-fit for merged cells options.AutoFitMergedCells = true; //Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); //Save the Excel file wb.Save(dataDir + "AutoFitMergedCells.xlsx"); }
public void CellsWorkbookPostAutofitWorkbookRowsTest() { // TODO uncomment below to test the method and replace null with proper value string name = BOOK1; AutoFitterOptions autoFitterOptions = new AutoFitterOptions(); int? startRow = 1; int? endRow = 100; bool? onlyAuto = true; string folder = TEMPFOLDER; UpdateDataFile(instance, folder, name); var response = instance.CellsWorkbookPostAutofitWorkbookRows(name, autoFitterOptions, startRow, endRow, onlyAuto, folder); Assert.IsInstanceOf <CellsCloudResponse>(response, "response is CellsCloudResponse"); Assert.AreEqual(response.Code, 200); }
public void CellsWorksheetsPostAutofitWorksheetColumnsTest() { string name = "Book1.xlsx"; string sheetName = "SHEET1"; int? firstColumn = 1; int? lastColumn = 10; AutoFitterOptions autoFitterOptions = null; int? firstRow = 1; int? lastRow = 19; string folder = null; new Config().UpdateDataFile(folder, name); var response = instance.CellsWorksheetsPostAutofitWorksheetColumns(name, sheetName, firstColumn, lastColumn, autoFitterOptions, firstRow, lastRow, folder); Console.WriteLine(response); }
public void CellsWorksheetsPostAutofitWorksheetRowTest() { string name = "Book1.xlsx"; string sheetName = "SHEET1"; int? rowIndex = 1; int? firstColumn = 1; int? lastColumn = 10; AutoFitterOptions autoFitterOptions = new AutoFitterOptions(); autoFitterOptions.AutoFitMergedCells = true; string folder = null; new Config().UpdateDataFile(folder, name); var response = instance.CellsWorksheetsPostAutofitWorksheetRow(name, sheetName, rowIndex, firstColumn, lastColumn, autoFitterOptions, folder); Console.WriteLine(response); }
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 wb = new Workbook(); //Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; //Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); //Merge the cells range.Merge(); //Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; //Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); //Set wrapping text on style.IsTextWrapped = true; //Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); //Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); //Set auto-fit for merged cells options.AutoFitMergedCells = true; //Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); //Save the Excel file wb.Save(dataDir+ "AutoFitMergedCells.xlsx"); }
public static void Run() { // ExStart:1 //Output directory string outputDir = RunExamples.Get_OutputDirectory(); // Instantiate a new Workbook Workbook wb = new Workbook(); // Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; // Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); // Merge the cells range.Merge(); // Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; // Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); // Set wrapping text on style.IsTextWrapped = true; // Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); // Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); // Set auto-fit for merged cells options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine; // Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); // Save the Excel file wb.Save(outputDir + "AutofitRowsforMergedCells.xlsx"); // ExEnd:1 Console.WriteLine("AutofitRowsforMergedCells executed successfully.\r\n"); }
public void CellsWorksheetsPostAutofitWorksheetColumnsTest() { // TODO uncomment below to test the method and replace null with proper value string name = BOOK1; string sheetName = SHEET1; int? firstColumn = 1; int? lastColumn = 10; AutoFitterOptions autoFitterOptions = new AutoFitterOptions(); int? firstRow = 1; int? lastRow = 19; string folder = TEMPFOLDER; UpdateDataFile(instance, folder, name); var response = instance.CellsWorksheetsPostAutofitWorksheetColumns(name, sheetName, firstColumn, lastColumn, autoFitterOptions, firstRow, lastRow, folder); Assert.IsInstanceOf <CellsCloudResponse>(response, "response is CellsCloudResponse"); Assert.AreEqual(response.Code, 200); }
public void CellsWorksheetsPostAutofitWorksheetRowsTest() { // TODO uncomment below to test the method and replace null with proper value string name = BOOK1; string sheetName = SHEET1; AutoFitterOptions autoFitterOptions = new AutoFitterOptions(); autoFitterOptions.AutoFitMergedCells = true; int? startRow = 1; int? endRow = 10; bool? onlyAuto = true; string folder = TEMPFOLDER; UpdateDataFile(folder, name); var response = instance.CellsWorksheetsPostAutofitWorksheetRows(name, sheetName, autoFitterOptions, startRow, endRow, onlyAuto, folder); Assert.IsInstanceOf <SaaSposeResponse>(response, "response is SaaSposeResponse"); Assert.AreEqual(response.Code, 200); }
public void CellsWorksheetsPostAutofitWorksheetRowTest() { // TODO uncomment below to test the method and replace null with proper value string name = BOOK1; string sheetName = SHEET1; int? rowIndex = 1; int? firstColumn = 1; int? lastColumn = 10; AutoFitterOptions autoFitterOptions = new AutoFitterOptions(); autoFitterOptions.AutoFitMergedCells = true; string folder = TEMPFOLDER; UpdateDataFile(folder, name); var response = instance.CellsWorksheetsPostAutofitWorksheetRow(name, sheetName, rowIndex, firstColumn, lastColumn, autoFitterOptions, folder); Assert.IsInstanceOf <SaaSposeResponse>(response, "response is SaaSposeResponse"); Assert.AreEqual(response.Code, 200); }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); string InputPath = dataDir + "Book1.xlsx"; // Instantiate a new Workbook Workbook wb = new Workbook(); // Get the first (default) worksheet Worksheet _worksheet = wb.Worksheets[0]; // Create a range A1:B1 Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2); // Merge the cells range.Merge(); // Insert value to the merged cell A1 _worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"; // Create a style object Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle(); // Set wrapping text on style.IsTextWrapped = true; // Apply the style to the cell _worksheet.Cells[0, 0].SetStyle(style); // Create an object for AutoFitterOptions AutoFitterOptions options = new AutoFitterOptions(); // Set auto-fit for merged cells options.AutoFitMergedCells = true; // Autofit rows in the sheet(including the merged cells) _worksheet.AutoFitRows(options); // Save the Excel file wb.Save(dataDir + "output.xlsx"); // ExEnd:1 }
private static System.IO.MemoryStream GetExcelStream(DataTable dt, string titleName) { #region //格式化单元格 //0 General General //1 Decimal 0 //2 Decimal 0.00 //3 Decimal #,##0 //4 Decimal #,##0.00 //5 Currency $#,##0;$-#,##0 //6 Currency $#,##0;[Red]$-#,##0 //7 Currency $#,##0.00;$-#,##0.00 //8 Currency $#,##0.00;[Red]$-#,##0.00 //9 Percentage 0% //10 Percentage 0.00% //11 Scientific 0.00E+00 //12 Fraction # ?/? //13 Fraction # / //14 Date m/d/yy //15 Date d-mmm-yy //16 Date d-mmm //17 Date mmm-yy //18 Time h:mm AM/PM //19 Time h:mm:ss AM/PM //20 Time h:mm //21 Time h:mm:ss //22 Time m/d/yy h:mm //37 Currency #,##0;-#,##0 //38 Currency #,##0;[Red]-#,##0 //39 Currency #,##0.00;-#,##0.00 //40 Currency #,##0.00;[Red]-#,##0.00 //41 Accounting _ * #,##0_ ;_ * "_ ;_ @_ //42 Accounting _ $* #,##0_ ;_ $* "_ ;_ @_ //43 Accounting _ * #,##0.00_ ;_ * "??_ ;_ @_ //44 Accounting _ $* #,##0.00_ ;_ $* "??_ ;_ @_ //45 Time mm:ss //46 Time h :mm:ss //47 Time mm:ss.0 //48 Scientific ##0.0E+00 //49 Text @ #endregion //格式化单元格 Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //为标题设置样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式 styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中 styleTitle.Font.Name = "宋体"; //文字字体 styleTitle.Font.Size = 18; //文字大小 styleTitle.Font.IsBold = true; //粗体 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = false; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 12; //文字大小 style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式数值 Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 12; //文字大小 style4.Number = 1; style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式数值 Style style5 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style5.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style5.Font.Name = "宋体"; //文字字体 style5.Font.Size = 12; //文字大小 style5.Number = 2; style5.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style5.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style5.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式数值 Style style6 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style6.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style6.Font.Name = "宋体"; //文字字体 style6.Font.Size = 12; //文字大小 style6.Font.Color = Color.Red; style6.Number = 2; int Colnum = dt.Columns.Count; //表格列数 int Rownum = dt.Rows.Count; //表格行数 var FHeaderRemark = string.Empty; if (dt.Columns.Contains("FHeaderRemark")) { if (dt.Rows.Count > 0) { FHeaderRemark = dt.Rows[0]["FHeaderRemark"].ToString(); } dt.Columns.Remove("FHeaderRemark"); Colnum--; } if (dt.Columns.Contains("FReportDetailCondition")) { dt.Columns.Remove("FReportDetailCondition"); Colnum--; } int StartIndex = 0;//记录开始行数 if (!string.IsNullOrEmpty(titleName)) { //生成行1 标题行 cells.Merge(StartIndex, 0, 1, Colnum); //合并单元格 cells[0, 0].PutValue(titleName); //填写内容 cells[0, 0].SetStyle(styleTitle); cells.SetRowHeight(0, 38); } if (FHeaderRemark != "") { string[] Remark = FHeaderRemark.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); if (Remark.Length > 0) { for (int i = 0; i < Remark.Length; i++) { StartIndex++; cells.Merge(StartIndex, 0, 1, Colnum); //合并单元格 cells[StartIndex, 0].PutValue(Remark[i]); //填写内容 cells[StartIndex, 0].SetStyle(style6); cells.SetRowHeight(0, 25); } } } //多表头输出 新增-By:Hdj 2017年4月5日11:42:03 var dataParentHeadIndex = StartIndex + 1; int ParentHead = 0; for (int i = 0; i < Colnum; i++) { cells[dataParentHeadIndex, i].SetStyle(style3); //获取当前列名 string clounName = dt.Columns[i].ColumnName; //找特殊符位置 int m = clounName.IndexOf("|"); //如果存在特殊符则为两级目录 if (m > 0) { //获取上级目录名称 ParentHead = 1; string paCloun = clounName.Substring(0, m); int r = 0;//设置colspan while (true) { //获取当前列名 string sonClounName = dt.Columns[i].ColumnName; //如果包含父级目录 if (sonClounName.Contains(paCloun)) { cells[dataParentHeadIndex, i].SetStyle(style2); r += 1; i += 1; } else { i -= 1; break; } } cells.Merge(dataParentHeadIndex, i - r + 1, 1, r); cells[dataParentHeadIndex, i - r + 1].PutValue(paCloun); cells.SetRowHeight(dataParentHeadIndex, 25); } } if (ParentHead == 1) { StartIndex++; } AutoFitterOptions options = new AutoFitterOptions(); options.OnlyAuto = true; options.AutoFitMergedCells = true; sheet.AutoFitRows(options); var dataHeadIndex = StartIndex + 1; StartIndex++; //生成行2 列名行 for (int i = 0; i < Colnum; i++) { var cloumnName = string.Empty; if (dt.Columns[i].ColumnName.IndexOf("|") > 0) { cloumnName = dt.Columns[i].ColumnName.Substring((dt.Columns[i].ColumnName.IndexOf("|") + 1)); } else { cloumnName = dt.Columns[i].ColumnName; } cells[dataHeadIndex, i].PutValue(cloumnName); cells[dataHeadIndex, i].SetStyle(style2); cells.SetRowHeight(dataHeadIndex, 25); } var dataIndex = StartIndex + 1; //生成数据行 for (int i = 0; i < Rownum; i++) { for (int k = 0; k < Colnum; k++) { if (dt.Columns[k].DataType == (new int()).GetType()) { cells[dataIndex + i, k].SetStyle(style4); cells[dataIndex + i, k].PutValue(dt.Rows[i][k].ToString(), true); } else if (dt.Columns[k].DataType == (new decimal()).GetType()) { cells[dataIndex + i, k].SetStyle(style5); cells[dataIndex + i, k].PutValue(dt.Rows[i][k].ToString(), true); } else { cells[dataIndex + i, k].SetStyle(style3); cells[dataIndex + i, k].PutValue(dt.Rows[i][k].ToString()); } } cells.SetRowHeight(dataIndex + i, 24); } return(workbook.SaveToStream()); //workbook.Save(path); }