private void SetStyle(Cells cells, dynamic reportDefinition) { object startingRowPosition = reportDefinition.RowPosition.Start; object endingRowPosition = reportDefinition.RowPosition.End; string startingColumnPosition = reportDefinition.ColumnPosition.Start; string endingColumnPoisiton = reportDefinition.ColumnPosition.End; string headerStart = string.Concat(startingColumnPosition, startingRowPosition); string headerEnd = string.Concat(endingColumnPoisiton, endingRowPosition); string TextAlignment = reportDefinition.TextAlign; cells[headerStart].PutValue(reportDefinition.content); Style headerStyleDefinition = _workbook.Styles[_workbook.Styles.Add()]; headerStyleDefinition.ForegroundColor = Color.Black; headerStyleDefinition.Font.Name = reportDefinition.Font.Name; headerStyleDefinition.Font.Size = reportDefinition.Font.Size; headerStyleDefinition.Font.Color = Color.FromName(reportDefinition.Font.Color); headerStyleDefinition.Font.IsBold = true; headerStyleDefinition.HorizontalAlignment = TextAlignmentType.Center; cells[headerStart].SetStyle(headerStyleDefinition); if (reportDefinition.MergeCells.ToLower() == "true") { Aspose.Cells.Range rng1 = _workbook.Worksheets[0].Cells.CreateRange(headerStart, headerEnd); rng1.Merge(); //int rowNmber = int.Parse(startingRowPosition.ToString()); } }
static void Main(string[] args) { //Instantiate a new Workbook. Workbook workbook = new Workbook(); //Specify the template excel file path. string myPath = "Book1.xls"; //Open the excel file. workbook.Open(myPath); //Get the range of cells i.e.., A1:C1. Aspose.Cells.Range rng1 = workbook.Worksheets[0].Cells.CreateRange("A1", "C1"); //Merge the cells. rng1.Merge(); Cells rng = workbook.Worksheets[0].Cells; //UnMerge the cell. rng.UnMerge(0, 0, 1, 3); //Save the file. workbook.Save("Book1.xls"); }
/// <summary> /// Merges or unmerges a range. /// </summary> /// <param name="range">The range.</param> /// <param name="mergeCells">True to merge cells, false to unmerge cells.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public static void SetMergeCells( this Range range, bool?mergeCells) { if (range == null) { throw new ArgumentNullException(nameof(range)); } if (mergeCells != null) { if ((bool)mergeCells) { range.Merge(); } else { range.UnMerge(); } } }
private void writeWSHeader() { string[] headerTitles; int[] formulaChooser; int[] isMerge; switch (fileType) { case FileTypes.memoQ: case FileTypes.memoQAllInfo: case FileTypes.memoQHTML: case FileTypes.memoQTrados: headerTitles = new string[] { "Path", "File name", "X-translated", "Context TM", "Repetitions", "100% Matches", "95% - 99%", "85% - 94%", "75% - 84%", "50% - 74%", "No Match", "Total", "Weighted", "DTP sent", "DTP provider", "Deadline", "Ready", "Imported to memoQ", "Ready to translate", "Translator", "Deadline", "Ready", "Reviewer", "Deadline", "Ready", "Ready to deliver" }; formulaChooser = new int[] { 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 1, 1, 3, 3, 3, 1, 1, 3, 1, 1, 3, 3 }; isMerge = new int[] { 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }; TotalColumn = 12; columnColors = new Color[] { c5, c5, c6, c6, c6, c6, c6, c6, c6, c6, c6, c7, c7, c1, c1, c1, c1, c2, c2, c3, c3, c2, c3, c3, c2, c4 }; Program.ColumnWidth = new int[] { -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 100, 100, 100, 120, 120, 100, 100, 100, 120, 120, 100, 120, 120, 100, 100 }; Program.isCondForm = new int[] { 1, 0, 0, 1, 1, 1, 0, 0, 1, 0, 0, 1, 1 }; break; case FileTypes.StudioHTML: case FileTypes.StudioXML: headerTitles = new string[] { "Path", "File name", "Locked", "PerfectMatch", "Context Match", "Repetitions", "Cross-file Repetitions", "100% Matches", "95% - 99%", "85% - 94%", "75% - 84%", "50% - 74%", "No Match", "Total", "Weighted", "DTP sent", "DTP provider", "Deadline", "Ready", "Imported to memoQ", "Ready to translate", "Translator", "Deadline", "Ready", "Reviewer", "Deadline", "Ready", "Ready to deliver" }; formulaChooser = new int[] { 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 1, 1, 3, 3, 3, 1, 1, 3, 1, 1, 3, 3 }; isMerge = new int[] { 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }; TotalColumn = 14; columnColors = new Color[] { c5, c5, c6, c6, c6, c6, c6, c6, c6, c6, c6, c6, c6, c7, c7, c1, c1, c1, c1, c2, c2, c3, c3, c2, c3, c3, c2, c4 }; Program.ColumnWidth = new int[] { -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 100, 100, 100, 120, 120, 100, 100, 100, 120, 120, 100, 120, 120, 100, 100 }; Program.isCondForm = new int[] { 1, 0, 0, 1, 1, 1, 0, 0, 1, 0, 0, 1, 1 }; grid = Helper.GridConvertToStudio(grid); break; default: throw new InvalidDataException("Invalid file type."); } for (int i = 0; i < TotalColumn - 3; i++) { Cell c = ws.Cells[0, 2 + i]; c.Value = Convert.ToDouble(grid[i]) / 100; Helper.AddBold(c); Helper.AddBackground(c, columnColors[2 + i]); Helper.AddNumberFormat(c, "0%"); } int columncount = headerTitles.Length; string lastrow = (translationFiles.Count + 3).ToString(); for (int i = 0; i < formulaChooser.Length; i++) { if (isMerge[i] == 1) { Aspose.Cells.Range r = ws.Cells.CreateRange(0, i, 2, 1); r.Merge(); } Cell c = ws.Cells[1 - isMerge[i], i]; c.Value = headerTitles[i]; Helper.AddBold(c); Helper.AddBackground(c, columnColors[i]); Helper.AddAlignment(c); string formula = ""; string column = CellsHelper.ColumnIndexToName(i); switch (formulaChooser[i]) { case 1: formula = "=COUNTA($" + column + "$4:$" + column + "$" + lastrow + ")"; break; case 2: formula = "=SUBTOTAL(9, $" + column + "$4:$" + column + "$" + lastrow + ")"; break; case 3: formula = "=COUNTIF($" + column + "$4:$" + column + "$" + lastrow + ",\"x\")"; break; } c = ws.Cells[2, i]; c.Formula = formula; Helper.AddBold(c); Helper.AddBackground(c, columnColors[i]); } ws.Cells[2, 0].Value = "Total:"; Helper.AddNumberFormat(ws.Cells[2, TotalColumn], "0"); }
public void downloadexcel(DataTable dt, string[] title, int[] ColumnWidth, string ReportTitleName) { GetData(); string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls"); //新建excel Workbook wb = new Workbook(); //设置字体样式 Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()]; style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; style1.Font.IsBold = true; //设置粗体 style1.Font.Size = 12; //设置字体大小 Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Center; style2.Font.Size = 10; //表头格式 Aspose.Cells.Style style3 = wb.Styles[wb.Styles.Add()]; style3.HorizontalAlignment = TextAlignmentType.Center; style3.Font.IsBold = true; //设置粗体 style3.Font.Size = 10; //设置字体大小 //sheet1 Worksheet ws = wb.Worksheets[0]; Cells cell = ws.Cells; ws.Name = "Telephone"; //合并第一行单元格 Aspose.Cells.Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length); range.Merge(); cell["A1"].PutValue(ReportTitleName); //标题 //给单元格关联样式 cell["A1"].SetStyle(style1); //报表名字 样式 //设置Execl列名 可以采用单独传值 for (int i = 0; i < title.Length; i++) { cell[1, i].PutValue(title[i]); cell[1, i].SetStyle(style3); } //设置单元格内容 int posStart = 2; int row = 0; for (int i = 0; i < dt.Rows.Count; i++) { DataRow Drow = dt.Rows[i]; cell[row + posStart, 0].PutValue(Drow["StaffID"].ToString()); cell[row + posStart, 0].SetStyle(style2); cell[row + posStart, 1].PutValue(Drow["StaffName"].ToString()); cell[row + posStart, 1].SetStyle(style2); cell[row + posStart, 2].PutValue(Drow["Station"].ToString()); cell[row + posStart, 2].SetStyle(style2); cell[row + posStart, 3].PutValue(Drow["Division"].ToString()); cell[row + posStart, 3].SetStyle(style2); cell[row + posStart, 4].PutValue(Drow["Class_Name"].ToString()); cell[row + posStart, 4].SetStyle(style2); cell[row + posStart, 5].PutValue(Drow["Batch"].ToString()); cell[row + posStart, 5].SetStyle(style2); cell[row + posStart, 6].PutValue(Drow["Course"].ToString()); cell[row + posStart, 6].SetStyle(style2); cell[row + posStart, 7].PutValue(Drow["Course_Ref"].ToString()); cell[row + posStart, 7].SetStyle(style2); cell[row + posStart, 8].PutValue(Convert.ToDateTime(Drow["Training_Date"]).ToString("yyyy-MM-dd")); cell[row + posStart, 8].SetStyle(style2); cell[row + posStart, 9].PutValue(Convert.ToDateTime(Drow["Training_Required_Date"]).ToString("yyyy-MM-dd")); cell[row + posStart, 9].SetStyle(style2); row++; } for (int i = 0; i < ColumnWidth.Length; i++) { cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString())); } Workbook wb1 = new Workbook(); //设置字体样式 Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()]; style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style11.Font.Name = "宋体"; style11.Font.IsBold = true; //设置粗体 style11.Font.Size = 12; //设置字体大小 Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()]; style21.HorizontalAlignment = TextAlignmentType.Center; style21.Font.Size = 10; //保存在服务器 wb.Combine(wb1); wb.Save(savepath); FileTo(ReportTitleName); }
//kill //public void Kill(Microsoft.Office.Interop.Excel.Application excel) //{ // excel.Quit(); // IntPtr t = new IntPtr(excel.Hwnd); // int k = 0; // GetWindowThreadProcessId(t, out k); // System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); // p.Kill(); //} protected void downloadlink(object sender, EventArgs e) { GetData1(); //设置EXCEL列宽 int[] ColumnWidth = { 10, 20, 20, 20, 50, 50, 20, 30, 30, 50, 20 }; //获取用户选择的excel文件名称 string ReportTitleName = "Authorization_List"; string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls"); //新建excel Workbook wb = new Workbook(); //设置字体样式 Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()]; style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; style1.Font.IsBold = true; //设置粗体 style1.Font.Size = 12; //设置字体大小 Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Center; style2.Font.Size = 10; //sheet1 Worksheet ws = wb.Worksheets[0]; Cells cell = ws.Cells; ws.Name = "Telephone"; //合并第一行单元格 Aspose.Cells.Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length); range.Merge(); cell["A1"].PutValue("Authorization List"); //标题 //给单元格关联样式 cell["A1"].SetStyle(style1); //报表名字 样式 //设置Execl列名 可以采用单独传值 cell[1, 0].PutValue("Staff No."); cell[1, 0].SetStyle(style2); cell[1, 1].PutValue("Staff Name"); cell[1, 1].SetStyle(style2); cell[1, 2].PutValue("Station"); cell[1, 2].SetStyle(style2); cell[1, 3].PutValue("Division"); cell[1, 3].SetStyle(style2); cell[1, 4].PutValue("Subject"); cell[1, 4].SetStyle(style2); cell[1, 5].PutValue("Rating"); cell[1, 5].SetStyle(style2); cell[1, 6].PutValue("Level"); cell[1, 6].SetStyle(style2); cell[1, 7].PutValue("Stamp"); cell[1, 7].SetStyle(style2); cell[1, 8].PutValue("ExpireDate"); cell[1, 8].SetStyle(style2); cell[1, 9].PutValue("Remarks"); cell[1, 9].SetStyle(style2); cell[1, 10].PutValue("Valid"); cell[1, 10].SetStyle(style2); //设置单元格内容 int posStart = 2; int row = 0; for (int i = 0; i < dt.Rows.Count; i++) { DataRow Drow = dt.Rows[i]; cell[row + posStart, 0].PutValue(Drow[1].ToString()); cell[row + posStart, 0].SetStyle(style2); cell[row + posStart, 1].PutValue(Drow[3].ToString()); cell[row + posStart, 1].SetStyle(style2); cell[row + posStart, 2].PutValue(Drow[14].ToString()); cell[row + posStart, 2].SetStyle(style2); cell[row + posStart, 3].PutValue(Drow[13].ToString()); cell[row + posStart, 3].SetStyle(style2); cell[row + posStart, 4].PutValue(Drow[2].ToString()); cell[row + posStart, 4].SetStyle(style2); cell[row + posStart, 5].PutValue(Drow[4].ToString()); cell[row + posStart, 5].SetStyle(style2); cell[row + posStart, 6].PutValue(Drow[5].ToString()); cell[row + posStart, 6].SetStyle(style2); cell[row + posStart, 7].PutValue(Drow[9].ToString()); cell[row + posStart, 7].SetStyle(style2); cell[row + posStart, 8].PutValue(Convert.ToDateTime(Drow[6].ToString()).ToString("dd-MMM-yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)); cell[row + posStart, 8].SetStyle(style2); cell[row + posStart, 9].PutValue(Drow[8].ToString()); cell[row + posStart, 9].SetStyle(style2); cell[row + posStart, 10].PutValue(Drow[7].ToString()); cell[row + posStart, 10].SetStyle(style2); row++; } for (int i = 0; i < ColumnWidth.Length; i++) { cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString())); } Workbook wb1 = new Workbook(); //设置字体样式 Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()]; style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style11.Font.Name = "宋体"; style11.Font.IsBold = true; //设置粗体 style11.Font.Size = 12; //设置字体大小 Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()]; style21.HorizontalAlignment = TextAlignmentType.Center; style21.Font.Size = 10; //保存在服务器 wb.Combine(wb1); wb.Save(savepath); FileTo(ReportTitleName); }
//Quang Huy 2014-02-19 /// <summary> /// Merge cells /// </summary> /// <param name="cellNameStart">tên của ô bắt đầu merge</param> /// <param name="cellNameEnd">tên của ô merge đến</param> public void Merge(string cellNameStart, string cellNameEnd) { Aspose.Cells.Range range = _worksheet.Cells.CreateRange(cellNameStart, cellNameEnd); range.Merge(); }