public static void Main()
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Open a template Excel file
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Get the first Picture in the first worksheet
Aspose.Cells.Drawing.Picture pic = worksheet.Pictures[0];
//Set the output image file path
string fileName = dataDir + "aspose-logo.out.Jpg";
string picformat = pic.ImageFormat.ToString();
//Note: you may evaluate the image format before specifying the image path
//Define ImageOrPrintOptions
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
//Specify the image format
printoption.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
//Save the image
pic.ToImage(fileName, printoption);
//ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a picture at the location of a cell whose row and column indices
//are 5 in the worksheet. It is "F6" cell
int pictureIndex = worksheet.Pictures.Add(5, 5, dataDir + "logo.jpg");
//Accessing the newly added picture
Aspose.Cells.Drawing.Picture picture = worksheet.Pictures[pictureIndex];
//Absolute positioning of the picture in unit of pixels
picture.Left = 60;
picture.Top = 10;
//Saving the Excel file
workbook.Save(dataDir + "book1.xls");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Open a template Excel file
Workbook workbook = new Workbook(sourceDir + "sampleExtractImagesFromWorksheets.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the first Picture in the first worksheet
Aspose.Cells.Drawing.Picture pic = worksheet.Pictures[0];
// Set the output image file path
string picformat = pic.ImageFormat.ToString();
// Note: you may evaluate the image format before specifying the image path
// Define ImageOrPrintOptions
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
// Specify the image format
printoption.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
// Save the image
pic.ToImage(outputDir + "outputExtractImagesFromWorksheets.jpg", printoption);
Console.WriteLine("ExtractImagesFromWorksheets executed successfully.\r\n");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
// Adding a picture at the location of a cell whose row and column indices
// Are 5 in the worksheet. It is "F6" cell
int pictureIndex = worksheet.Pictures.Add(5, 5, dataDir + "logo.jpg");
// Accessing the newly added picture
Aspose.Cells.Drawing.Picture picture = worksheet.Pictures[pictureIndex];
// Absolute positioning of the picture in unit of pixels
picture.Left = 60;
picture.Top = 10;
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls");
// ExEnd:1
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Open a template Excel file
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Get the first Picture in the first worksheet
Aspose.Cells.Drawing.Picture pic = worksheet.Pictures[0];
//Set the output image file path
string fileName = dataDir + "aspose-logo.Jpg";
string picformat = pic.ImageFormat.ToString();
//Note: you may evaluate the image format before specifying the image path
//Define ImageOrPrintOptions
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
//Specify the image format
printoption.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
//Save the image
pic.ToImage(fileName, printoption);
}
private void process()
{
excel.Workbook workbook = new excel.Workbook(this.ucFilesAndButtons1.TxbTemplateFileName.Text);
excel.Worksheet sheet = workbook.Worksheets[0];
int count = sheet.TextBoxes.Count();
for (int i = 0; i < count; i++)
{
excel.Drawing.TextBox t = sheet.TextBoxes[i];
if (t.Text == "二维码")
{
t.HasLine = false;
t.Text = "";
int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
excel.Drawing.Picture picture = sheet.Pictures[x];
picture.LeftToCorner = t.LeftToCorner;
picture.TopToCorner = t.TopToCorner;
picture.Width = t.Width;
picture.Height = t.Height;
break;
}
}
//excel.Drawing.TextBox txb = sheet.TextBoxes[0];
//txb.HasLine = false;
//txb.Text = "123";
//int x = sheet.Pictures.Add(5, 5, this.ucFilesAndButtons1.TxbImageFilePath.Text);
//excel.Drawing.Picture picture = sheet.Pictures[x];
//picture.LeftToCorner = txb.LeftToCorner;
//picture.TopToCorner = txb.TopToCorner;
//picture.Width = txb.Width;
//picture.Height = txb.Height;
workbook.Save(this.ucFilesAndButtons1.TxbExportFileName.Text);
workbook.Save(this.ucFilesAndButtons1.TxbPdf.Text);
MessageBox.Show("ok");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create a workbook object
// Open the template file
Workbook workbook = new Workbook(sourceDir + "sampleCopyingPicture.xlsx");
// Get the Picture from the "Picture" worksheet.
Aspose.Cells.Drawing.Picture source = workbook.Worksheets["Sheet1"].Pictures[0];
// Save Picture to Memory Stream
MemoryStream ms = new MemoryStream(source.Data);
// Copy the picture to the Result Worksheet
workbook.Worksheets["Sheet2"].Pictures.Add(source.UpperLeftRow, source.UpperLeftColumn, ms, source.WidthScale, source.HeightScale);
// Save the Worksheet
workbook.Save(outputDir + "outputCopyingPicture.xlsx");
Console.WriteLine("CopyingPicture executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open the existing file.
Workbook workbook = new Workbook(dataDir + "chart.xls");
// Get an image file to the stream.
FileStream stream = new FileStream(dataDir + "logo.jpg", FileMode.Open, FileAccess.Read);
// Get the designer chart in the second sheet.
Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Charts.Chart chart = sheet.Charts[0];
// Add a new picture to the chart.
Aspose.Cells.Drawing.Picture pic0 = chart.Shapes.AddPictureInChart(50, 50, stream, 40, 40);
// Get the lineformat type of the picture.
Aspose.Cells.Drawing.LineFormat lineformat = pic0.Line;
// Set the dash style.
lineformat.DashStyle = Aspose.Cells.Drawing.MsoLineDashStyle.Solid;
// Set the line weight.
lineformat.Weight = 4;
// Save the excel file.
workbook.Save(dataDir + "chart.out.xls");
// ExEnd:1
}
public static void Run()
{
// Open the existing file.
Workbook workbook = new Workbook(sourceDir + "sampleAddingPictureInChart.xls");
// Get an image file to the stream.
FileStream stream = new FileStream(sourceDir + "sampleAddingPictureInChart.png", FileMode.Open, FileAccess.Read);
// Get the designer chart in the second sheet.
Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Charts.Chart chart = sheet.Charts[0];
// Add a new picture to the chart.
Aspose.Cells.Drawing.Picture pic0 = chart.Shapes.AddPictureInChart(50, 50, stream, 200, 200);
// Get the lineformat type of the picture.
Aspose.Cells.Drawing.LineFormat lineformat = pic0.Line;
// Set the dash style.
lineformat.DashStyle = Aspose.Cells.Drawing.MsoLineDashStyle.Solid;
// Set the line weight.
lineformat.Weight = 4;
// Save the excel file.
workbook.Save(outputDir + "outputAddingPictureInChart.xls");
Console.WriteLine("AddingPictureInChart executed successfully.");
}
private async Task <Aspose.Cells.Drawing.Picture> StyleImageExcel(Aspose.Cells.Drawing.Picture pic)
{
pic.Height = 40;
pic.Width = 80;
pic.Top = 10;
pic.Left = 20;
return(await Task.FromResult(pic));
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Open the template file
Workbook workbook = new Workbook(sourceDir + "sampleCopyShapesBetweenWorksheets.xlsx");
// Get the Picture from the "Picture" worksheet.
Aspose.Cells.Drawing.Picture picturesource = workbook.Worksheets["Picture"].Pictures[0];
// Save Picture to Memory Stream
MemoryStream ms = new MemoryStream(picturesource.Data);
// Copy the picture to the Result Worksheet
workbook.Worksheets["Result"].Pictures.Add(picturesource.UpperLeftRow, picturesource.UpperLeftColumn, ms, picturesource.WidthScale, picturesource.HeightScale);
// Save the Worksheet
workbook.Save(outputDir + "outputCopyShapesBetweenWorksheets_Picture.xlsx");
//-----------------------------------
//-----------------------------------
// Open the template file
workbook = new Workbook(sourceDir + "sampleCopyShapesBetweenWorksheets.xlsx");
// Get the Chart from the "Chart" worksheet.
Aspose.Cells.Charts.Chart chartsource = workbook.Worksheets["Chart"].Charts[0];
Aspose.Cells.Drawing.ChartShape cshape = chartsource.ChartObject;
// Copy the Chart to the Result Worksheet
workbook.Worksheets["Result"].Shapes.AddCopy(cshape, 5, 0, 2, 0);
// Save the Worksheet
workbook.Save(outputDir + "outputCopyShapesBetweenWorksheets_Chart.xlsx");
//-----------------------------------
//-----------------------------------
// Open the template file
workbook = new Workbook(sourceDir + "sampleCopyShapesBetweenWorksheets.xlsx");
// Get the Shapes from the "Control" worksheet.
Aspose.Cells.Drawing.ShapeCollection shape = workbook.Worksheets["Control"].Shapes;
// Copy the Textbox to the Result Worksheet
workbook.Worksheets["Result"].Shapes.AddCopy(shape[0], 5, 0, 2, 0);
// Save the Worksheet
workbook.Save(outputDir + "outputCopyShapesBetweenWorksheets_Control.xlsx");
Console.WriteLine("CopyShapesBetweenWorksheets executed successfully.");
}
public async Task <IActionResult> SearchExcel([FromBody] AuditRecSearch model)
{
var data = await _service.SearchExcel(model, "2");
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\WT_Summary_Report.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
//lưu data với ASPO vào excel
designer.SetDataSource("result", data);
designer.Process();
Worksheet ws = designer.Workbook.Worksheets[0];
for (var i = 1; i <= data.Count; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data[i - 1].Before_Picture;
var filePathAfter = "wwwroot\\uploaded\\images\\" + data[i - 1].After_Picture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 17, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
if (System.IO.File.Exists(filePathAfter))
{
//Add picture và set Size cho image
var pictureIndex = ws.Pictures.Add(i, 18, filePathAfter);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
if (data.Count != 0)
{
designer.Workbook.Save(stream, SaveFormat.Xlsx);
}
// designer.Workbook.Save (path + "Test.xlsx", SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WT_Summary_Report" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public static void Run()
{
// ExStart:CopyPictureBetweenWorksheets
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open the template file
Workbook workbook = new Workbook(dataDir + "sample.xlsx");
// Get the Picture from the "Picture" worksheet.
Aspose.Cells.Drawing.Picture picturesource = workbook.Worksheets["Picture"].Pictures[0];
// Save Picture to Memory Stream
MemoryStream ms = new MemoryStream(picturesource.Data);
// Copy the picture to the Result Worksheet
workbook.Worksheets["Result"].Pictures.Add(picturesource.UpperLeftRow, picturesource.UpperLeftColumn, ms, picturesource.WidthScale, picturesource.HeightScale);
// Save the Worksheet
workbook.Save(dataDir + "PictureCopied_out.xlsx");
// ExEnd:CopyPictureBetweenWorksheets
// ExStart:CopyChartBetweenWorksheets
// Get the Chart from the "Chart" worksheet.
Aspose.Cells.Charts.Chart chartsource = workbook.Worksheets["Chart"].Charts[0];
Aspose.Cells.Drawing.ChartShape cshape = chartsource.ChartObject;
// Copy the Chart to the Result Worksheet
workbook.Worksheets["Result"].Shapes.AddCopy(cshape, 20, 0, 2, 0);
// Save the Worksheet
workbook.Save(dataDir + "ChartCopied_out.xlsx");
// ExEnd:CopyChartBetweenWorksheets
// ExStart:CopyControlsAndOtherDrawingObjects
// Open the template file
workbook = new Workbook(dataDir + "sample2.xlsx");
// Get the Shapes from the "Control" worksheet.
Aspose.Cells.Drawing.ShapeCollection shape = workbook.Worksheets["Control"].Shapes;
// Copy the Textbox to the Result Worksheet
workbook.Worksheets["Result"].Shapes.AddCopy(shape[0], 5, 0, 2, 0);
// Copy the Oval Shape to the Result Worksheet
workbook.Worksheets["Result"].Shapes.AddCopy(shape[1], 10, 0, 2, 0);
// Save the Worksheet
workbook.Save(dataDir + "ControlsCopied_out.xlsx");
// ExEnd:CopyControlsAndOtherDrawingObjects
}
public async Task <ActionResult> ExportExcelScoreRecordDetail(string recordId)
{
var data = await _auditRateService.GetScoreRecordDetail(recordId);
var Building = await _auditPicDService.GetBuidingByID(data.auditRateM.Building);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Sixs_Score_Record_Detail_Template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
// Gán giá trị tĩnh
ws.Cells["B2"].PutValue(data.auditRateM.Record_Date);
ws.Cells["D2"].PutValue(data.auditRateM.PDC_Name);
ws.Cells["F2"].PutValue(Building);
ws.Cells["B3"].PutValue(data.auditRateM.Updated_By);
ws.Cells["D3"].PutValue(data.auditRateM.Updated_Time);
ws.Cells["F3"].PutValue(data.auditRateM.Line_ID_2_Name);
ws.Cells["F4"].PutValue(data.auditRateM.Audit_Type2);
designer.SetDataSource("result", data.listAuditRateD);
designer.Process();
for (var i = 6; i <= data.listAuditRateD.Count + 5; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data.listAuditRateD[i - 6].UplloadPicture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 6, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Sixs_Score_Record_Detail" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Insert a linked picture (from Web Address) to B2 Cell.
Aspose.Cells.Drawing.Picture pic = workbook.Worksheets[0].Shapes.AddLinkedPicture(1, 1, 100, 100, "http://www.aspose.com/Images/aspose-logo.jpg");
//Set the height and width of the inserted image.
pic.HeightInch = 1.04;
pic.WidthInch = 2.6;
//Save the Excel file.
workbook.Save(dataDir + "outLinkedPicture.xlsx");
}
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();
//Insert a linked picture (from Web Address) to B2 Cell.
Aspose.Cells.Drawing.Picture pic = workbook.Worksheets[0].Shapes.AddLinkedPicture(1, 1, 100, 100, "http://www.aspose.com/Images/aspose-logo.jpg");
//Set the height and width of the inserted image.
pic.HeightInch = 1.04;
pic.WidthInch = 2.6;
//Save the Excel file.
workbook.Save(dataDir + "outLinkedPicture.out.xlsx");
//ExEnd:1
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
Workbook workbook = new Workbook();
// Insert a linked picture (from Web Address) to B2 Cell.
Aspose.Cells.Drawing.Picture pic = workbook.Worksheets[0].Shapes.AddLinkedPicture(1, 1, 100, 100, "http://www.aspose.com/Images/aspose-logo.jpg");
// Set the height and width of the inserted image.
pic.HeightInch = 1.04;
pic.WidthInch = 2.6;
// Save the Excel file.
workbook.Save(outputDir + "outputInsertLinkedPicture.xlsx");
Console.WriteLine("InsertLinkedPicture executed successfully.");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create a workbook object
//Open the template file
Workbook workbook = new Workbook(dataDir + "aspose-sample.xlsx");
//Get the Picture from the "Picture" worksheet.
Aspose.Cells.Drawing.Picture source = workbook.Worksheets["Sheet1"].Pictures[0];
//Save Picture to Memory Stream
MemoryStream ms = new MemoryStream(source.Data);
//Copy the picture to the Result Worksheet
workbook.Worksheets["Sheet2"].Pictures.Add(source.UpperLeftRow, source.UpperLeftColumn, ms, source.WidthScale, source.HeightScale);
//Save the Worksheet
workbook.Save(dataDir + "Shapes.xlsx");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create a new Workbook.
//Open the existing file.
Workbook workbook = new Workbook(dataDir + "chart.xls");
//Get an image file to the stream.
FileStream stream = new FileStream(dataDir + "logo.jpg", FileMode.Open, FileAccess.Read);
//Get the designer chart in the second sheet.
Worksheet sheet = workbook.Worksheets[1];
Aspose.Cells.Charts.Chart chart = sheet.Charts[0];
//Add a new picture to the chart.
Aspose.Cells.Drawing.Picture pic0 = chart.Shapes.AddPictureInChart(50, 50, stream, 40, 40);
//Get the lineformat type of the picture.
Aspose.Cells.Drawing.MsoLineFormat lineformat = pic0.LineFormat;
//Set the line color.
lineformat.ForeColor = Color.Red;
//Set the dash style.
lineformat.DashStyle = Aspose.Cells.Drawing.MsoLineDashStyle.Solid;
//set the line weight.
lineformat.Weight = 4;
//Set the line style.
lineformat.Style = Aspose.Cells.Drawing.MsoLineStyle.ThickThin;
//Save the excel file.
workbook.Save(dataDir + "chart_out.xls");
}
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);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a picture at the location of a cell whose row and column indices
//are 5 in the worksheet. It is "F6" cell
int pictureIndex = worksheet.Pictures.Add(5, 5, dataDir + "logo.jpg");
//Accessing the newly added picture
Aspose.Cells.Drawing.Picture picture = worksheet.Pictures[pictureIndex];
//Positioning the picture proportional to row height and colum width
picture.UpperDeltaX = 200;
picture.UpperDeltaY = 200;
//Saving the Excel file
workbook.Save(dataDir + "book1.out.xls");
//ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a workbook object
// Open the template file
Workbook workbook = new Workbook(dataDir + "aspose-sample.xlsx");
// Get the Picture from the "Picture" worksheet.
Aspose.Cells.Drawing.Picture source = workbook.Worksheets["Sheet1"].Pictures[0];
// Save Picture to Memory Stream
MemoryStream ms = new MemoryStream(source.Data);
// Copy the picture to the Result Worksheet
workbook.Worksheets["Sheet2"].Pictures.Add(source.UpperLeftRow, source.UpperLeftColumn, ms, source.WidthScale, source.HeightScale);
// Save the Worksheet
workbook.Save(dataDir + "Shapes.out.xlsx");
// ExEnd:1
}
public async Task <ActionResult> ExportExcelAspose([FromQuery] PaginationParams param, string text, int checkExport)
{
var data = await _articleCategoryService.GetArticleCategoryWithPaginations(param, text, false);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\ArticleCategory\\ArticleCategoryListTemplate.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();
int index = 2;
for (int i = 0; i < data.Result.Count; i++)
{
ws.Cells["A" + index].PutValue(i + 1);
index++;
}
var index2 = 1;
foreach (var item in data.Result)
{
string file = "";
if (item.Status == true)
{
file = _webHostEnvironment.WebRootPath + "\\icons\\ok-512.png";
}
else
{
file = _webHostEnvironment.WebRootPath + "\\icons\\circle-outline-512.png";
}
Aspose.Cells.Drawing.Picture pic = ws.Pictures[ws.Pictures.Add(index2, 3, file)];
pic.Height = 20;
pic.Width = 20;
pic.Top = 5;
pic.Left = 40;
index2++;
}
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.CustomPaperSize(12.5, 8);
ws.PageSetup.FitToPagesTall = 0;
ws.PageSetup.SetHeader(0, "&D &T");
ws.PageSetup.SetHeader(1, "&B Article Category");
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_Category_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + fileExtension));
}
public async Task <IActionResult> ExportExcelWTTrackingList([FromBody] AuditRecSearch model)
{
var data = await _service.SearchExcel(model, "2");
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\WT_Tracking_List.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
//gán giá trị tĩnh
Worksheet ws = designer.Workbook.Worksheets[0];
if (data.Count != 0)
{
ws.Cells["B2"].PutValue(data[0].Record_Time);
ws.Cells["B3"].PutValue(data[0].PDC);
ws.Cells["B4"].PutValue(data[0].Attendees);
ws.Cells["D2"].PutValue(data[0].Building);
ws.Cells["D3"].PutValue(data[0].Line);
ws.Cells["F2"].PutValue(data[0].Model_Name);
ws.Cells["F3"].PutValue(data[0].Model_No);
ws.Cells["I2"].PutValue(data[0].Chief);
ws.Cells["I3"].PutValue(data[0].Recorder);
}
//lưu data với ASPO vào excel
designer.SetDataSource("result", data);
designer.Process();
//Chèn image vào excel và set lại rows
for (var i = 6; i <= data.Count + 5; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data[i - 6].Before_Picture;
var filePathAfter = "wwwroot\\uploaded\\images\\" + data[i - 6].After_Picture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 7, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
if (System.IO.File.Exists(filePathAfter))
{
//Add picture và set Size cho image
var pictureIndex = ws.Pictures.Add(i, 8, filePathAfter);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
// designer.Workbook.Save (path + "Test.xlsx", SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WT_Tracking_List" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
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> ExportExcelDetailAspose([FromQuery] string productCateID, string productID, int checkExport)
{
var data = await _productService.GetProductByID(productCateID, productID.ToInt());
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Product\\ProductDetailTemplate.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Cell cell = designer.Workbook.Worksheets[0].Cells["A1"];
Worksheet ws = designer.Workbook.Worksheets[0];
Cells cells = ws.Cells;
designer.SetDataSource("result", data);
designer.Process();
Style style = designer.Workbook.CreateStyle();
style.IsTextWrapped = true;
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
StyleFlag flg = new StyleFlag();
flg.All = true;
int index = 2;
ws.Cells["A" + (index)].PutValue(data.Product_Cate_ID);
ws.Cells["B" + (index)].PutValue(data.Product_Name);
ws.Cells["G" + (index)].PutValue(data.Price);
ws.Cells["H" + (index)].PutValue(data.Amount);
ws.Cells["I" + (index)].PutValue(data.Update_By);
ws.Cells["J" + (index)].PutValue(data.Update_Time.ToString());
string folderImage = _webHostEnvironment.WebRootPath + "\\uploaded\\images\\product\\";
string folderVideo = _webHostEnvironment.WebRootPath + "\\uploaded\\video\\product\\";
if (data.FileImages != null)
{
string[] listImage = data.FileImages.Split(";");
foreach (var image in listImage)
{
if (image != "")
{
Aspose.Cells.Drawing.Picture pic = ws.Pictures[ws.Pictures.Add(index - 1, 10, folderImage + image)];
pic = await StyleImageExcel(pic);
ws.Cells.SetRowHeight(index - 1, 45);
index++;
}
}
}
int index1 = 2;
if (data.FileVideos != null)
{
string[] listVideo = data.FileVideos.Split(";");
foreach (var video in listVideo)
{
if (video != "")
{
Aspose.Cells.Drawing.Picture pic = ws.Pictures[ws.Pictures.Add(index1 - 1, 11, folderVideo + "video.jpg")];
pic = await StyleImageExcel(pic);
ws.Cells.SetRowHeight(index1 - 1, 45);
index1++;
}
}
}
if (data.FileVideos != null || data.FileImages != null)
{
int index3 = index >= index1 ? index : index1;
// Merge column not image, video
int[] number = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
foreach (var item in number)
{
cells.Merge(1, item, index3 - 2, 1);
}
// Set style
for (int i = 1; i < index3 - 1; i++)
{
string[] text = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L" };
foreach (var item in text)
{
ws.Cells[item + (i + 1)].SetStyle(style, flg);
}
}
string fileNew = _dropzoneService.CheckTrueFalse(data.New);
string fileIsSale = _dropzoneService.CheckTrueFalse(data.IsSale);
string fileHotSale = _dropzoneService.CheckTrueFalse(data.Hot_Sale);
string fileStatus = _dropzoneService.CheckTrueFalse(data.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 = 20 * (index3 - 2);
iconNew.Left = iconIsSale.Left = iconHotSale.Left = iconStatus.Left = 40;
}
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 Product");
ws.PageSetup.SetFooter(0, "&B SYSTEM BY MINH HIEU");
ws.PageSetup.SetFooter(2, "&P/&N");
ws.PageSetup.PrintQuality = 2400;
designer.Workbook.Save(stream, SaveFormat.Pdf);
fileKind = "application/pdf";
fileExtension = ".pdf";
}
byte[] result = stream.ToArray();
return(File(result, fileKind, "Product_Detail_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + fileExtension));
}
private void DownExcelCate2()
{
IList <MonthlyReportDetail> IsMissList = A_MonthlyreportdetailOperator.Instance.GetVMissDetail(SysId, FinYear, FinMonth).ToList();
for (int a = 0; a < IsMissList.Count; a++)
{
IsMissList[a].CompanyName = C_TargetOperator.Instance.GetTarget(IsMissList[a].TargetID).TargetName;
}
ExcelEngine excel = new ExcelEngine();
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(ExcelTempletePath, "补回期限管理模板V1.xls");
FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
designer.Workbook = new Workbook(fileStream);
WorksheetCollection worksheets = designer.Workbook.Worksheets;
#region 样式
Aspose.Cells.Style style1 = designer.Workbook.Styles[designer.Workbook.Styles.Add()];
Aspose.Cells.Style style2 = designer.Workbook.Styles[designer.Workbook.Styles.Add()];
Aspose.Cells.Style style3 = designer.Workbook.Styles[designer.Workbook.Styles.Add()];
Aspose.Cells.Style style4 = designer.Workbook.Styles[designer.Workbook.Styles.Add()];
#region style1
style1.Font.Size = 12;
style1.Font.Name = "Arial";
style1.Font.IsBold = true;
style1.ForegroundColor = System.Drawing.Color.FromArgb(184, 204, 228);
style1.Pattern = BackgroundType.Solid;
style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style1.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style1.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
#endregion
#region style2
style2.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style2.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style2.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
#endregion
#region style4
style4.Font.Size = 12;
style4.Font.Name = "Arial";
style4.Font.IsBold = true;
style4.ForegroundColor = System.Drawing.Color.FromArgb(184, 204, 228);
style4.Pattern = BackgroundType.Solid;
style4.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style4.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style4.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
#endregion
#region style3
style3.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style3.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style3.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
style3.IsTextWrapped = true;
#endregion
#endregion
int rowStart = 3;
int colStart = 0; // 开始列
excel.SetCustomProperty(worksheets[0], "SystemID", SystemModel.ID.ToString());
excel.SetCustomProperty(worksheets[0], "SheetName", "CommitUpdate");
excel.SetCustomProperty(worksheets[0], "Time", FinYear + "-" + FinMonth);
worksheets[0].Name = SystemModel.SystemName;
worksheets[0].Cells[0, 1].PutValue(SystemModel.SystemName + "补回期限填报");
worksheets[0].Cells[1, 1].PutValue(FinYear.ToString() + "年" + FinMonth + "月");
int NO = 0;
for (int i = 0; i < IsMissList.Count; i++)
{
NO = NO + 1;
worksheets[0].Cells[rowStart, colStart + 1].SetStyle(style1);
worksheets[0].Cells[rowStart, colStart + 1].PutValue(NO);
worksheets[0].Cells[rowStart, colStart + 2].SetStyle(style1);
worksheets[0].Cells[rowStart, colStart + 2].PutValue(IsMissList[i].CompanyName);
worksheets[0].Cells[rowStart, colStart + 3].SetStyle(style4);
if (IsMissList[i].PromissDate != null)
{
worksheets[0].Cells[rowStart, colStart + 3].PutValue(IsMissList[i].PromissDate.Value.Year + "/" + IsMissList[i].PromissDate.Value.Month + "/" + IsMissList[i].PromissDate.Value.Day);
}
else
{
worksheets[0].Cells[rowStart, colStart + 3].PutValue("--");
}
worksheets[0].Cells[rowStart, colStart + 4].SetStyle(style2);
if (IsMissList[i].CurrentMonthCommitDate != null)
{
worksheets[0].Cells[rowStart, colStart + 4].PutValue(IsMissList[i].CurrentMonthCommitDate.Value.Year + "/" + IsMissList[i].CurrentMonthCommitDate.Value.Month + "/" + IsMissList[i].CurrentMonthCommitDate.Value.Day);
}
worksheets[0].Cells[rowStart, colStart + 5].SetStyle(style3);
worksheets[0].Cells[rowStart, colStart + 5].PutValue(IsMissList[i].CurrentMonthCommitReason);
worksheets[0].Cells[rowStart, colStart + 7].PutValue(IsMissList[i].CompanyID);
worksheets[0].Cells[rowStart, colStart + 8].PutValue(IsMissList[i].TargetID);
worksheets[0].Cells[rowStart, colStart + 6].SetStyle(style1);
if (IsMissList[i].Counter > 0)
{
int pictureIndex = worksheets[0].Pictures.Add(rowStart, colStart + 6, ImageFilePath + "\\image" + IsMissList[i].Counter + ".png");
Aspose.Cells.Drawing.Picture picture = worksheets[0].Pictures[pictureIndex];
picture.Left = 60;
picture.Top = 10;
}
rowStart = rowStart + 1;
}
MemoryStream stream = designer.Workbook.SaveToStream();
fileStream.Close();
fileStream.Dispose();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "utf-8";
string dateNow = DateTime.Now.ToString("HHmmss");
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(SystemModel.SystemName + fileName, System.Text.Encoding.UTF8) + FinYear.ToString() + FinMonth.ToString("D2") + "_" + dateNow + ".xls");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.End();
}
