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();
}
