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";
// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(InputPath, FileMode.Open);
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Auto-fitting the 3rd row of the worksheet
worksheet.AutoFitRow(1);
// Saving the modified Excel file
workbook.Save(dataDir + "output.xlsx");
// Closing the file stream to free all resources
fstream.Close();
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string output1Path = dataDir + "Output.xlsx";
string output2Path = dataDir + "Output.out.ods";
Workbook workbook = new Workbook();
Style style = workbook.CreateBuiltinStyle(BuiltinStyleType.Title);
Cell cell = workbook.Worksheets[0].Cells["A1"];
cell.PutValue("Aspose");
cell.SetStyle(style);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumn(0);
worksheet.AutoFitRow(0);
workbook.Save(output1Path);
Console.WriteLine("File saved {0}", output1Path);
workbook.Save(output2Path);
Console.WriteLine("File saved {0}", output1Path);
// ExEnd:1
}
private void StyleChanger(ref Worksheet ws)
{
int rowmin = 7;
int rowmax = 44;
int columnmin = 1;
int columnmax = ws.Cells.MaxColumn;
if (columnmax < 4)
{
columnmax = 4;
}
for (int i = 0; i < columnmax; i++)
{
double columnWidth = ws.Cells.Columns[i].Width;
columnWidth = columnWidth * 1.15;
ws.Cells.Columns[i].Width = columnWidth;
}
for (int row = rowmin - 1; row < rowmax; row++)
{
for (int column = columnmin - 1; column < columnmax; column++)
{
Cell c = ws.Cells[row, column];
Style style = c.GetStyle();
style.IsTextWrapped = true;
c.SetStyle(style);
}
ws.AutoFitRow(row);
}
ws.PageSetup.PrintArea = "A1:" + (char)(ws.Cells.MaxDataColumn + 'A') + (ws.Cells.MaxDataRow + 1);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MiscDataTable.xls");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumn(1);
worksheet.AutoFitRow(1);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
public void CreateXlsHeader(Worksheet worksheet, int row, int column, bool isExcell)
{
SetCellStyle(worksheet, row, column, true, isExcell);
worksheet.Cells[row, column].PutValue("");
worksheet.Cells[row, column + 1].PutValue("Date");
worksheet.Cells[row, column + 2].PutValue("Principal");
worksheet.Cells[row, column + 3].PutValue("Interest");
worksheet.Cells[row, column + 4].PutValue("Fees");
worksheet.Cells[row, column + 5].PutValue("Rebate");
worksheet.Cells[row, column + 6].PutValue("Total");
worksheet.Cells[row, column + 7].PutValue("Status");
worksheet.Cells[row, column + 8].PutValue("Description");
SetHeaderBackgroundColor(worksheet, row, column, isExcell);
worksheet.AutoFitRow(row);
}
public static void Main(string[] args)
{
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string output1Path = dataDir + "Output.xlsx";
string output2Path = dataDir + "Output.out.ods";
Workbook workbook = new Workbook();
Style style = workbook.CreateBuiltinStyle(BuiltinStyleType.Title);
Cell cell = workbook.Worksheets[0].Cells["A1"];
cell.PutValue("Aspose");
cell.SetStyle(style);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitColumn(0);
worksheet.AutoFitRow(0);
workbook.Save(output1Path);
Console.WriteLine("File saved {0}", output1Path);
workbook.Save(output2Path);
Console.WriteLine("File saved {0}", output1Path);
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public void DataGridViewToExcel(DataGridView dgv)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
try
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
sheet.Name = "手术查询";
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 = 12; //文字大小
style2.Font.IsBold = true; //粗体
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 = 10; //文字大小
style3.IsTextWrapped = true; //单元格内容自动换行
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;
int Colnum = dgv.Columns.Count; //表格列数
int Rownum = dgv.Rows.Count; //表格行数
int visibleCols = dgv.Columns.Cast <DataGridViewColumn>().Where(x => x.Visible).Count();
//生成行1 标题行
cells.Merge(0, 0, 1, visibleCols); //合并单元格
cells[0, 0].PutValue(string.Format("手术查询（{0}至{1}）", this.dateTimePicker1.Value.ToString("yyyy-MM-dd"), this.dateTimePicker2.Value.ToString("yyyy-MM-dd"))); //填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);
int hideIndex = 0;
//生成行2 列名行
for (int i = 0; i + hideIndex < Colnum; i++)
{
while (i + hideIndex < Colnum && !dgv.Columns[i + hideIndex].Visible)
{
hideIndex++;
}
if (i + hideIndex >= Colnum)
{
continue;
}
cells[1, i].PutValue(dgv.Columns[i + hideIndex].HeaderText);
cells[1, i].SetStyle(style2);
cells.SetRowHeight(1, 25);
//设置表页的列宽度自适应
sheet.AutoFitColumn(i, 0, cells.MaxRow);
cells.SetColumnWidthPixel(i, cells.GetColumnWidthPixel(i) + 2);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
hideIndex = 0;
for (int k = 0; k + hideIndex < Colnum; k++)
{
while (k + hideIndex < Colnum && !dgv.Columns[k + hideIndex].Visible)
{
hideIndex++;
}
if (k + hideIndex >= Colnum)
{
continue;
}
cells[2 + i, k].PutValue(Convert.ToString(dgv.Rows[i].Cells[k + hideIndex].FormattedValue) ?? "");
cells[2 + i, k].SetStyle(style3);
}
cells.SetRowHeight(2 + i, 24);
//设置表页的行高自适应
sheet.AutoFitRow(i + 2);
cells.SetColumnWidthPixel(i + 2, cells.GetColumnWidthPixel(i + 2) + 6);
}
workbook.Save(fileNameString);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
MessageBox.Show(fileNameString + "，导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private static void exportSummaryDetail(DataTable taskTable, DataTable subTaskTable, Worksheet ws, String[] SelectColumns, String[] SummaryOverviews)
{
string[] taskAttributes = null; //split up selected columns into task and sub task attributes. Task attributes exist in taskTable while subTask attributes exist in subTask table. Summary overview attributers exist in both tables.
string[] subTaskAttributes = null;
DataRow currentRow = null;
int taskRowsPrinted = 0; //need to alternate between printing tasks and sub tasks. Need to know where you stopped in the table, so you can start from that position next time you print.
int subTaskRowsPrinted = 0;
int rowCount = 1; //keep track all rows printed.
int newRowsPrinted = 0; //keep track of new rows printed to be added to the above row counts.
taskAttributes = Array.FindAll <string>(SelectColumns, x => x.StartsWith("Task")); //task attributes all begin with "task". Sub task attributres all begin with "Sub-Task".
subTaskAttributes = Array.FindAll <string>(SelectColumns, x => x.StartsWith("Sub-Task"));
int mergedCellCount = subTaskAttributes.Length > taskAttributes.Length ? subTaskAttributes.Length : taskAttributes.Length; //the number of columns that will be in the table, used for creating cell headers and the like.
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.IsBold = true;
cellStyle.Font.Underline = FontUnderlineType.Single;
cellStyle.IsTextWrapped = true;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
cellStyle.IsTextWrapped = true;
bool autosize = true;; //if true let aspose size the columns, if false manually size columns so that all columns fit in pdf page width.
List <double> columSizeRatio = getColumnSizeRatio(taskTable, subTaskTable, taskAttributes, subTaskAttributes, mergedCellCount, ref autosize); //this list contains the ratio of column sizes, where the ratio is defined as the neccessary size of the column devided by the total size of all columns. This list is null if autosize is set to true.
if (autosize == false)
{
for (int i = 0; i < mergedCellCount; i++) //resize columns so they fit on one page.
{
double size = (double)9.0 * columSizeRatio[i]; //in ladscape mode with 1 inch margins the available page space is about 9 inches.
ws.Cells.SetColumnWidthInch(i, size);
}
}
ws.Cells.Merge(0, 0, 1, mergedCellCount);
ws.Cells["A1"].PutValue("Workload Overview");
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
rowCount += 2;
rowCount += newRowsPrinted;
while (taskRowsPrinted < taskTable.Rows.Count) //the task table is the driver of the loop, because it is not possible to have orphan sub tasks; that is there should never be an instance where a sub task has attributes distinct from all higher level tasks
{
DataRow oldRow = currentRow;
currentRow = taskTable.Rows[taskRowsPrinted]; //get the next row to be printed
newRowsPrinted = printOverviews(currentRow, oldRow, SummaryOverviews, ws, rowCount, mergedCellCount);
rowCount += newRowsPrinted;
newRowsPrinted = printTasks(taskAttributes, taskTable, ws, rowCount, ref taskRowsPrinted, currentRow, SummaryOverviews, mergedCellCount, alternateColor: false);
rowCount += newRowsPrinted;
newRowsPrinted = printTasks(subTaskAttributes, subTaskTable, ws, rowCount, ref subTaskRowsPrinted, currentRow, SummaryOverviews, mergedCellCount, alternateColor: true);
rowCount += newRowsPrinted;
}
if (autosize == true)
{
ws.AutoFitColumns();
}
else
{
ws.AutoFitRows();
}
ws.Name = "Summary Detail";
return;
}
private static void exportResourceOverview(DataTable dt, Worksheet ws, string worksheetName)
{
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.IsBold = true;
cellStyle.Font.Underline = FontUnderlineType.Single;
ws.Cells.ImportDataTable(dt, true, "A3");
cellStyle.IsTextWrapped = true;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
ws.Cells.Merge(0, 0, 1, 3);
ws.Cells["A1"].PutValue(worksheetName);
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
cellStyle.Font.Size = cellStyle.Font.Size - 2;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.Underline = FontUnderlineType.None;
for (int i = 0; i < dt.Columns.Count; i++) //header row
{
ws.Cells[2, i].SetStyle(cellStyle);
}
cellStyle.Font.IsBold = false;
for (int i = 3; i < dt.Rows.Count + 2; i++) //body
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0) //left column is left alligned.
{
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
ws.Cells[i, j].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
ws.Cells[i, j].SetStyle(cellStyle);
}
}
}
cellStyle.Font.IsBold = true;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Black);
for (int i = 0; i < dt.Columns.Count; i++) //Total All row
{
if (i == 0)
{
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
ws.Cells[dt.Rows.Count + 2, i].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
ws.Cells[dt.Rows.Count + 2, i].SetStyle(cellStyle);
}
}
ws.AutoFitColumns();
ws.Name = worksheetName;
return;
}
private static void exportSummaryOverviews(DataTable parentTable, DataTable childTable, Worksheet ws, String[] SummaryOverview1 = null, String[] SummaryOverview2 = null)
{
int rowCount = 5; //holds the integer value of the current row in the document to write too.
int childRowsPrinted = 0;
string headerRow = "";
string[] parentColumns = object.ReferenceEquals(SummaryOverview1, null) ? SummaryOverview2 : SummaryOverview1; //it is possible for there to be only one summary level.
bool level1 = !Object.ReferenceEquals(SummaryOverview1, null);
bool level2 = !Object.ReferenceEquals(SummaryOverview2, null);
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.Underline = FontUnderlineType.Single;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
if (level1) //if there is a level 1 summary, add the column names to the header row
{
headerRow = String.Join(",", SummaryOverview1);
}
if (level1 && level2) //if there is both a level one and level two summary, add a \ to deliminate the too.
{
headerRow += " \\ ";
}
if (level2) //if there is a level 2 summary, add the column names to the header row.
{
headerRow += String.Join(",", SummaryOverview2);
}
if (headerRow.Length > 90) //if the header row is really long then you have to manually set the column widths so everthing fits.
{
ws.Cells.SetColumnWidthInch(0, 1);
ws.Cells.SetColumnWidthInch(1, 6);
ws.Cells.SetColumnWidthInch(2, 1);
ws.Cells.SetColumnWidthInch(3, 1);
}
ws.Cells.Merge(0, 0, 1, 4);
ws.Cells["A1"].PutValue("Summary Overview");
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
cellStyle.Font.Size = cellStyle.Font.Size - 2;
ws.Cells.Merge(2, 0, 1, 4);
ws.Cells["A3"].PutValue(headerRow);
ws.Cells["A3"].SetStyle(cellStyle);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells["A4"].PutValue("Rank");
ws.Cells["A4"].SetStyle(cellStyle);
ws.Cells["B4"].PutValue(headerRow); //print table header row
ws.Cells["B4"].SetStyle(cellStyle);
ws.Cells["C4"].PutValue("Tasks");
ws.Cells["C4"].SetStyle(cellStyle);
ws.Cells["D4"].PutValue("Sub Tasks");
ws.Cells["D4"].SetStyle(cellStyle);
foreach (DataRow parentRow in parentTable.Rows)
{
printParentRows(parentColumns, parentRow, rowCount, ws);
rowCount++;
if (!Object.ReferenceEquals(childTable, null)) //if there is a child table, print the child rows.
{
int newRowsPrinted = 0;
newRowsPrinted = printChildRows(parentColumns, SummaryOverview2, parentRow, childTable, rowCount, childRowsPrinted, ws);
rowCount += newRowsPrinted;
childRowsPrinted += newRowsPrinted;
}
}
if (headerRow.Length > 90)
{
ws.AutoFitRows();
}
else
{
ws.AutoFitColumns();
}
printTotalRow(parentTable, ws, rowCount);
ws.Name = "Summary Overviews";
return;
}
private void FillReport(Workbook wb, Dictionary <RowData, int> rowDataIndex)
{
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass)
{
break;
}
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
#region 填入驗證結果
SortedList <int, RowData> markedRow = new SortedList <int, RowData>();
foreach (var row in _ErrorRows.Keys)
{
if (rowDataIndex.ContainsKey(row))
{
markedRow.Add(rowDataIndex[row], row);
}
}
foreach (var row in _WarningRows.Keys)
{
if (rowDataIndex.ContainsKey(row) && !markedRow.ContainsKey(rowDataIndex[row]))
{
markedRow.Add(rowDataIndex[row], row);
}
}
foreach (var index in markedRow.Keys)
{
RowData row = markedRow[index];
if (_ErrorRows.ContainsKey(row))
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_ErrorRows[row]);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[index, 0].Name);
wb.Worksheets[0].Cells[index, 0].Style = errorStyle;
wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
if (_WarningRows.ContainsKey(row))
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_WarningRows[row]);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[index, 0].Name);
if (!_ErrorRows.ContainsKey(row))
{
wb.Worksheets[0].Cells[index, 0].Style = errorStyle;
wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
}
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
}
#endregion
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
}
public async Task <ActionResult> ExportExcelListAspose([FromQuery] PaginationParams param, string text, int checkExport, string productCateID, string productName, int checkSearch)
{
PageListUtility <Product_Dto> data;
if (checkSearch == 1)
{
data = await _productService.GetProductWithPaginations(param, text, false);
}
else
{
data = await _productService.SearchProductWithPaginations(param, productCateID, productName, false);
}
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Product\\ProductListTemplate.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Cell cell = designer.Workbook.Worksheets[0].Cells["A1"];
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data.Result);
designer.Process();
Style styleDecimal = ws.Cells["G2"].GetStyle();
styleDecimal.Custom = "0.00";
Style styleDateTime = ws.Cells["J2"].GetStyle();
styleDateTime.Custom = "dd/MM/yyyy hh:mm:ss";
for (int i = 1; i <= data.Result.Count; i++)
{
ws.AutoFitRow(i);
ws.Cells["G" + (i + 1)].SetStyle(styleDecimal);
ws.Cells["J" + (i + 1)].SetStyle(styleDateTime);
}
int index = 2;
foreach (var item in data.Result)
{
string fileNew = _dropzoneService.CheckTrueFalse(item.New);
string fileIsSale = _dropzoneService.CheckTrueFalse(item.IsSale);
string fileHotSale = _dropzoneService.CheckTrueFalse(item.Hot_Sale);
string fileStatus = _dropzoneService.CheckTrueFalse(item.Status);
Aspose.Cells.Drawing.Picture iconNew = ws.Pictures[ws.Pictures.Add(1, 2, fileNew)];
Aspose.Cells.Drawing.Picture iconIsSale = ws.Pictures[ws.Pictures.Add(1, 3, fileIsSale)];
Aspose.Cells.Drawing.Picture iconHotSale = ws.Pictures[ws.Pictures.Add(1, 4, fileHotSale)];
Aspose.Cells.Drawing.Picture iconStatus = ws.Pictures[ws.Pictures.Add(1, 5, fileStatus)];
iconNew.Height = iconIsSale.Height = iconHotSale.Height = iconStatus.Height = 20;
iconNew.Width = iconIsSale.Width = iconHotSale.Width = iconStatus.Width = 20;
iconNew.Top = iconIsSale.Top = iconHotSale.Top = iconStatus.Top = 5;
iconNew.Left = iconIsSale.Left = iconHotSale.Left = iconStatus.Left = 40;
ws.Cells.SetRowHeight(index - 1, 22.5);
index++;
}
MemoryStream stream = new MemoryStream();
string fileKind = "";
string fileExtension = "";
if (checkExport == 1)
{
designer.Workbook.Save(stream, SaveFormat.Xlsx);
fileKind = "application/xlsx";
fileExtension = ".xlsx";
}
if (checkExport == 2)
{
// custom size ( width: in, height: in )
ws.PageSetup.FitToPagesTall = 0;
ws.PageSetup.SetHeader(0, "&D &T");
ws.PageSetup.SetHeader(1, "&B Article");
ws.PageSetup.SetFooter(0, "&B SYSTEM BY MINH HIEU");
ws.PageSetup.SetFooter(2, "&P/&N");
ws.PageSetup.PrintQuality = 1200;
designer.Workbook.Save(stream, SaveFormat.Pdf);
fileKind = "application/pdf";
fileExtension = ".pdf";
}
byte[] result = stream.ToArray();
return(File(result, fileKind, "Product_List_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + fileExtension));
}
public async Task <ActionResult> ExportExcelListAspose([FromQuery] PaginationParams param, string text, int checkExport, string articleCateID, string articleName, int checkSearch)
{
PageListUtility <Article_Dto> data;
if (checkSearch == 1)
{
data = await _articleService.GetArticleWithPaginations(param, text, false);
}
else
{
data = await _articleService.SearchArticleWithPaginations(param, articleCateID, articleName, false);
}
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Article\\ArticleListTemplate.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Cell cell = designer.Workbook.Worksheets[0].Cells["A1"];
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data.Result);
designer.Process();
Style style = ws.Cells["F2"].GetStyle();
style.Custom = "dd/MM/yyyy hh:mm:ss";
for (int i = 1; i <= data.Result.Count; i++)
{
ws.Cells["F" + (i + 1)].SetStyle(style);
}
int index = 1;
foreach (var item in data.Result)
{
if (item.Content.Length > 70)
{
ws.AutoFitRow(index);
}
else
{
ws.Cells.SetRowHeight(index, 22.5);
}
string file = _dropzoneService.CheckTrueFalse(item.Status);
Aspose.Cells.Drawing.Picture pic = ws.Pictures[ws.Pictures.Add(index, 3, file)];
pic = await StyleImageCheck(pic);
index++;
}
MemoryStream stream = new MemoryStream();
string fileKind = "";
string fileExtension = "";
if (checkExport == 1)
{
designer.Workbook.Save(stream, SaveFormat.Xlsx);
fileKind = "application/xlsx";
fileExtension = ".xlsx";
}
if (checkExport == 2)
{
// custom size ( width: in, height: in )
ws.PageSetup.FitToPagesTall = 0;
ws.PageSetup.SetHeader(0, "&D &T");
ws.PageSetup.SetHeader(1, "&B Article");
ws.PageSetup.SetFooter(0, "&B SYSTEM BY MINH HIEU");
ws.PageSetup.SetFooter(2, "&P/&N");
ws.PageSetup.PrintQuality = 1200;
designer.Workbook.Save(stream, SaveFormat.Pdf);
fileKind = "application/pdf";
fileExtension = ".pdf";
}
byte[] result = stream.ToArray();
return(File(result, fileKind, "Article_List_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + fileExtension));
}
private void btnXuatChiTiet_Click(object sender, EventArgs e)
{
if (dtSinhVien != null && dtSinhVien.Rows.Count > 0)
{
CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!");
#region Xử lý table
Lib.clsDataTableHelper clsTable = new Lib.clsDataTableHelper();
DataTable dtMonHoc = clsTable.SelectDistinct(dtChiTiet, new string[] { "SapXep", "XL_MonHocTrongKyID", "MaMonHoc", "TenMonHoc", "SoHocTrinh" });
foreach (DataRow dr in dtMonHoc.Rows)
{
if (!dtSinhVien.Columns.Contains("DT_" + dr["XL_MonHocTrongKyID"]))
{
dtSinhVien.Columns.Add("DT_" + dr["XL_MonHocTrongKyID"], typeof(double));
dtSinhVien.Columns.Add("DTK_" + dr["XL_MonHocTrongKyID"], typeof(double));
}
}
DataRow[] arrDr;
foreach (DataRow dr in dtSinhVien.Rows)
{
arrDr = dtChiTiet.Select("SV_SinhVienID = " + dr["SV_SinhVienID"]);
foreach (DataRow _dr in arrDr)
{
dr["DT_" + _dr["XL_MonHocTrongKyID"]] = _dr["DiemThi"];
dr["DTK_" + _dr["XL_MonHocTrongKyID"]] = _dr["DiemTongKetMon"];
}
}
#endregion
#region Chuẩn bị tệp excel để ghi dữ liệu
Workbook exBook = new Workbook();
exBook.Open(Application.StartupPath + "\\Template\\DanhSachXetHocBongChiTiet.xls", FileFormatType.Excel2003);
_exSheet = exBook.Worksheets[0];
_range = _exSheet.Cells;
#endregion
SaveFileDialog sv = new SaveFileDialog();
sv.FileName = "DanhSachXetHocBongChiTiet -Lop" + cmbLop.Text.ToUpper() + "- Ky" + Program.HocKy.ToString() + " -Nam " + Program.NamHoc + ".xls";
sv.Filter = "Excel file (*.xls)|*.xls";
sv.Title = "Xuất dữ liệu";
if (sv.ShowDialog() == DialogResult.OK)
{
#region Đổ dữ liệu vào báo cáo
int DongBatDau = 9, DongFieldName = 5, CotBatDau = 9;
_range["B5"].PutValue("Xuất sắc:" +
string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + xuatsac) * 100 / dtChiTiet.Rows.Count))) +
". Giỏi:" + string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + gioi) * 100 / dtChiTiet.Rows.Count).ToString())) + ". Khá:" + string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + kha) * 100 / dtChiTiet.Rows.Count).ToString())) + ". TB Khá:" + string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + TBKha) * 100 / dtChiTiet.Rows.Count).ToString())) + ". Trung bình:" + string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + trungbinh) * 100 / dtChiTiet.Rows.Count))) + ". Kém:" + string.Format("{0:N2}%",
float.Parse("0" + (float.Parse("0" + Kem) * 100 / dtChiTiet.Rows.Count).ToString())));
// Thay thế dữ liệu trên file
Dictionary <string, string> DuLieuThayThe = GetDuLieuThayTheBaoCao();
DuLieuThayThe.Add("HocKy", Program.HocKy.ToString());
DuLieuThayThe.Add("NamHoc", Program.NamHoc);
if (cmbLop.Text != "")
{
DuLieuThayThe.Add("LopHoc", "LỚP " + cmbLop.Text.ToUpper());
}
else
{
DuLieuThayThe.Add("LopHoc", " ");
}
foreach (KeyValuePair <string, string> pair in DuLieuThayThe)
{
_exSheet.Replace(pair.Key, pair.Value);
}
Range rngCopy, rngPaste;
// Thêm tiêu đề cho các cột điểm môn học
//rngCopy = _exSheet.Cells.CreateRange(DongFieldName + 1, CotBatDau, 3, 2);
//rngPaste = _exSheet.Cells.CreateRange(DongFieldName + 1, CotBatDau, 3, 2);
if (cmbLop.Text != "")
{
}
// Set tiều đề cho môn cuối cùng trước, sau đó insert ngược
_range[DongFieldName, CotBatDau].PutValue("DT_" + dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["XL_MonHocTrongKyID"]);
_range[DongFieldName, CotBatDau + 1].PutValue("DTK_" + dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["XL_MonHocTrongKyID"]);
_range[DongFieldName + 1, CotBatDau].PutValue(dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["TenMonHoc"]);
_exSheet.Cells.Merge(DongFieldName + 1, CotBatDau, 1, 2);
_range[DongFieldName + 2, CotBatDau].PutValue((dtMonHoc.Rows.Count).ToString());
_exSheet.Cells.Merge(DongFieldName + 2, CotBatDau, 1, 2);
int stt = 1;
for (int i = dtMonHoc.Rows.Count - 2; i >= 0; i--)
{
_exSheet.Cells.InsertColumn(CotBatDau);
_exSheet.Cells.InsertColumn(CotBatDau);
//rngPaste.Copy(rngCopy);
//rngPaste.CopyStyle(rngCopy);
_range[DongFieldName, CotBatDau].PutValue("DT_" + dtMonHoc.Rows[i]["XL_MonHocTrongKyID"]);
_range[DongFieldName, CotBatDau + 1].PutValue("DTK_" + dtMonHoc.Rows[i]["XL_MonHocTrongKyID"]);
_range[DongFieldName + 1, CotBatDau].PutValue(dtMonHoc.Rows[i]["TenMonHoc"]);
_exSheet.Cells.Merge(DongFieldName + 1, CotBatDau, 1, 2);
_range[DongFieldName + 2, CotBatDau].PutValue((i + 1).ToString());
_exSheet.Cells.Merge(DongFieldName + 2, CotBatDau, 1, 2);
_range[DongFieldName + 3, CotBatDau].PutValue("Điểm thi");
_range[DongFieldName + 3, CotBatDau + 1].PutValue("ĐTK");
stt++;
}
_exSheet.Cells.DeleteColumn(CotBatDau - 1);
// Thêm các dòng và đưa style vào các dòng được thêm
rngCopy = _exSheet.Cells.CreateRange(DongBatDau + 1, 0, 1, 100);
int _count = dtSinhVien.Rows.Count, k, DongHienTai = DongBatDau;
if (_count > 3)
{
_range.InsertRows(DongBatDau + 1, _count - 3);
}
else
{
_range.DeleteRows(DongBatDau + 1, 3 - _count);
}
for (int i = 1; i < _count - 1; i++)
{
rngPaste = _exSheet.Cells.CreateRange(DongBatDau + i, 0, 1, 100);
rngPaste.Copy(rngCopy);
rngPaste.CopyStyle(rngCopy);
_exSheet.Cells.SetRowHeight(DongBatDau + i, rngCopy.RowHeight);
}
string _FieldName = "";
for (int i = 0; i < _count; i++)
{
k = 0;
_FieldName = ("" + _exSheet.Cells[DongFieldName, 0].Value).Trim();
while (_FieldName != "")
{
if (dtSinhVien.Columns.Contains(_FieldName) || _FieldName.ToUpper() == "STT")
{
if (_FieldName.ToUpper() == "STT")
{
_range[DongHienTai, k].PutValue(i + 1);
}
else if (_FieldName.IndexOf("=") >= 0)
{
_range[DongHienTai, k].Formula = _FieldName.Replace("[i]", (DongBatDau + i + 1).ToString()).Replace("'", "");
}
else
{
_range[DongHienTai, k].PutValue(dtSinhVien.Rows[i][_FieldName]);
}
}
k++;
_FieldName = ("" + _exSheet.Cells[DongFieldName, k].Value).Trim();
}
_exSheet.AutoFitRow(DongHienTai);
DongHienTai++;
}
// Xóa dòng field name
_exSheet.Cells.DeleteRow(DongFieldName);
#endregion
#region Lưu và mở file excel
#region Lưu và mở file excel
string strTenFileExcelMoi = sv.FileName;
exBook.Save(strTenFileExcelMoi, FileFormatType.Excel2003);
CloseWaitDialog();
ThongBao("Xuất dữ liệu thành công.");
try
{
Process.Start(strTenFileExcelMoi);
}
catch (Exception ex)
{
ThongBaoLoi("Đã có lỗi: " + ex.Message);
}
#endregion
//if (!Directory.Exists(Program.DuongDanThuMucBaoCao))
// Directory.CreateDirectory(Program.DuongDanThuMucBaoCao);
//string strTenFileExcelMoi = Program.DuongDanThuMucBaoCao + "\\" + string.Format("BaoCaoXetHocBongChiTiet_{0}.xls", DateTime.Now.ToString("yyyyMMdd"));
//exBook.Save(strTenFileExcelMoi, FileFormatType.Excel2003);
//CloseWaitDialog();
#endregion
}
else
{
ThongBao("Không có dữ liệu để xuất Excel.");
}
}
}
public string ExportExcel_Public_SpecialRowNew(bool ischeckcolumn, out bool hasRecord, out Workbook outWorkbook, List <string> otherInfo, List <string> redFieldInfo)
{
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
string savePath = string.Empty;
hasRecord = false;
int k = 0;
int rowmun = this.Rows.Count;
Worksheet sheet = workbook.Worksheets[0];
for (int j = (ischeckcolumn) ? 1 : 0; j < this.Columns.Count; j++)
{
var col = this.Columns[j];
if (col.Visible)
{
var cell = cells[0, k];
cell.PutValue(col.HeaderText);
Aspose.Cells.Style styple = cell.GetStyle();
styple.IsTextWrapped = true;
cell.SetStyle(styple);
if (redFieldInfo.Count > 0)
{
foreach (string strRedInfo in redFieldInfo)
{
if (col.HeaderText.IndexOf(strRedInfo, System.StringComparison.Ordinal) != -1)
{
cell.Style.Font.Color = Color.Red;
}
}
}
int row = 0;
for (int i = 0; i < this.Rows.Count; i++)
{
var obj = this[0, i].Value;
if (ischeckcolumn)
{
if (obj == null || !bool.Parse(obj.ToString()))
{
continue;
}
if (obj is bool && bool.Parse(obj.ToString()))
{
cell = cells[row + 1, k];
cell.Style.Font.Color = this[j, i].Style.ForeColor;
obj = this[j, i].Value;
if (obj != null)
{
var typ = obj.GetType();
if (typ == typeof(DateTime))
{
cell.Style.Custom = "yyyy-mm-dd hh:mm:ss";
cell.PutValue(obj.ToString());
}
else if (typ == typeof(string))
{
var str = obj.ToString();
if (str.IsNum())
{
cell.PutValue(double.Parse(str));
}
else
{
cell.PutValue(str);
}
}
else
{
cell.PutValue(obj);
}
hasRecord = true;
}
}
}
else
{
if (obj == null)
{
continue;
}
sheet.AutoFitRow(row + 1);
cell = cells[row + 1, k];
cell.Style.Font.Color = this[j, i].Style.ForeColor;
//cell.Style.Font.Color = Color.Yellow;
obj = this[j, i].Value;
if (obj != null)
{
var typ = obj.GetType();
if (typ == typeof(DateTime))
{
cell.Style.Custom = "yyyy-mm-dd hh:mm:ss";
cell.PutValue(obj.ToString());
}
else if (typ == typeof(string))
{
var str = obj.ToString();
if (str.IsNum())
{
cell.PutValue(double.Parse(str));
}
else
{
cell.PutValue(str);
}
}
else
{
cell.PutValue(obj);
}
hasRecord = true;
}
}
row++;
}
k++;
}
}
if (otherInfo != null)
{
int irow = rowmun + 2;
foreach (string strinfo in otherInfo)
{
irow++;
var cell1 = cells[irow, 0];
var obj1 = strinfo;
cell1.PutValue(obj1);
}
}
sheet.AutoFitRow(0);
outWorkbook = workbook;
return(savePath);
}
/// <summary>
/// PutValue theo định dạng
/// </summary>
/// <remarks>LongLD</remarks>
private Worksheet PutValueInType(Worksheet worksheet, CellOption cellOption)
{
switch (cellOption.Type.ToLower())
{
case "date":
var date = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, date);
cellOption.Style.Custom = "dd/MM/yyyy";
break;
case "datetime":
var dateT = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, dateT);
cellOption.Style.Custom = "dd/MM/yyyy HH:ss";
break;
case "datetimefull":
var dateTf = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, dateTf);
cellOption.Style.Custom = "dd/MM/yyyy HH:mm:ss";
break;
case "number":
var n = !string.IsNullOrEmpty(cellOption.Text) ?
decimal.Parse(cellOption.Text) : 0;
worksheet = PutValue(worksheet, cellOption, cellOption.Text, n);
cellOption.Style.Custom = "#,##0";
break;
case "numbern2":
var n2 = !string.IsNullOrEmpty(cellOption.Text) ?
decimal.Parse(cellOption.Text, CultureInfo.GetCultureInfo("en-us")) : 0;
worksheet = PutValue(worksheet, cellOption, cellOption.Text, n2);
cellOption.Style.Custom = "#,##0.00";
break;
default:
cellOption.Style.Number = '@';
worksheet.Cells[cellOption.Row, cellOption.Column].PutValue(cellOption.Text);
break;
}
if (cellOption.Merge)
{
if (cellOption.AutoFitRow)
{
worksheet.AutoFitRow(cellOption.Row, cellOption.Row + cellOption.TotalRow, cellOption.Column, cellOption.Column + cellOption.TotalColumn);
}
for (int i = 0; i < cellOption.TotalRow; i++)
{
for (int j = 0; j < cellOption.TotalColumn; j++)
{
worksheet.Cells[cellOption.Row + i, cellOption.Column + j].SetStyle(cellOption.Style);
}
}
}
else
{
if (cellOption.AutoFitRow)
{
worksheet.AutoFitRow(cellOption.Row, cellOption.Row + 1, cellOption.Column, cellOption.Column + 1);
}
worksheet.Cells[cellOption.Row, cellOption.Column].SetStyle(cellOption.Style);
}
return(worksheet);
}
