Excel processing control Spire.XLS FAQ_spire.xls.dll filter data_Love sharing Xiaonizi’s blog

In order to facilitate users to quickly grasp and understand Spire.XLS, the editor below summarizes and organizes a series of common problems in the trial process of trial users, hoping to help everyone!

Click here→→<<Download the latest version of Spire.XLS>>

(1) How to add images to Excel?

A : Please call the Add method to add the image to the worksheet of the Excel file. Full code:

Workbook workbook = new Workbook();
 Worksheet sheet = workbook. Worksheets[0];

 //insert the picture day.jpg into the sheet and place it in the cell "B3"
 sheet.Pictures.Add(3, 2, "day.jpg");
 workbook.SaveToFile("result.xlsx");

(2) How to insert a new row in the worksheet of the Excel file?

A : Please call the method InsertRow to add a new row to the worksheet of the Excel file. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[0];

 //add a new row in the third row
 sheet. InsertRow(3);
 workbook.SaveToFile("result.xlsx");

(3) How to set the printing area in XLS?

A: You can set the PrintArea property of the worksheet to do this. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[0];

 //set print area from cell "B2" to cell "F8"
 sheet.PageSetup.PrintArea = "B2:F8";
 workbook.SaveToFile("result.xlsx");

(4) How to copy formatted cells?

A: Spire.XLS provides you with a method called Copy to copy cells with formatting. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet1 = workbook. Worksheets[0];
 Worksheet sheet3 = workbook. Worksheets[2];

 //copy cell "B2" in sheet1 to cell "C6" in sheet3
 sheet1.Range[3, 2].Copy(sheet3.Range[6,3]);
 workbook.SaveToFile("result.xlsx");

(5) How to convert XLS to PDF?

A : First, you need to add the references Spire.Pdf and Spire.Common to your project. Then create a PdfDocument instance and a PDF converter object. Finally, the Convert method is called to convert the XLS to PDF. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 PdfConverter pdfConverter = new PdfConverter(workbook);
 PdfDocument pdfDocument = new PdfDocument();

 //settings of result PDF document
 pdfDocument.PageSettings.Orientation = PdfPageOrientation.Landscape;
 pdfDocument.PageSettings.Width = 970;
 pdfDocument.PageSettings.Height = 850;
 PdfConverterSettings settings = new PdfConverterSettings();
 settings.TemplateDocument = pdfDocument;

 //convert XLS to PDF using PdfConverter
 pdfDocument = pdfConverter. Convert(settings);
 pdfDocument.SaveToFile("result.pdf");

(6) How to merge cells in XLS?

A: Spire.XLS provides you with a method called Merge to merge cells in XLS. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[0];

 //merges cells "B3" "B4"
 sheet.Range["B3:B4"].Merge();
 workbook.SaveToFile("result.xlsx");

(7) How to rearrange the worksheets in the XLS file?

A : Does Spire.XLS provide you with a method called MoveWorksheet to rearrange worksheets in an XLS file? Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[3];

 //move the fourth worksheet sheet to the first position of the worksheets
 sheet.MoveWorksheet(0);
 workbook.SaveToFile("result.xlsx");

(8) How to delete columns in the excel worksheet?

A: Spire.XLS provides you with a method called DeleteColumn to delete a column. This method immediately affects the order of the sheet collection. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[1];
 //delete the second column
 sheet. DeleteColumn(2);

 //delete the fourth column
 sheet. DeleteColumn(3);
 workbook.SaveToFile("result.xlsx");

(9) How to format numbers within a specified range in XLS?

A : You can use the property NumberFormat of the target cell to format the NumberValue of cellrange. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample.xlsx");
 Worksheet sheet = workbook. Worksheets[0];

 //set number format in specified range
 sheet.Range[2, 2, 6, 6].NumberFormat = "$#,##0.00";
 sheet.Range["C3"].NumberValue = 3240.689;
 sheet.Range["D4"].NumberValue = 5230.123;
 workbook.SaveToFile("result.xlsx");

(10) How to add a formula in a cell?

A : You can add a formula simply by setting the formula property of the cell. �Complete code:

Workbook workbook = new Workbook();
 Worksheet worksheet = workbook. Worksheets[0];

 //add formula =IF(H7>0,(IF(F7 > 0,(H7-F7)/F7,"""")),"""") to cell "J7"
 string formula = @"=IF(H7>0,(IF(F7 > 0,(H7-F7)/F7,"""")),"""")";
 worksheet.Range["J7"].Formula = formula;
 worksheet.Range["F7"].NumberValue = 5;
 worksheet.Range["H7"].NumberValue = 4;
 workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);

(11) How to merge multiple workbooks?

A: Spire.XLS provides you with a method called AddCopy to merge Excel files. Full code:

Workbook workbook = new Workbook();
 workbook.LoadFromFile("sample1.xlsx");
 Workbook workbookDest = new Workbook();
 workbookDest.LoadFromFile("sample2.xlsx");
 //merge workbook with workbookDest
 workbookDest. Worksheets. AddCopy(workbook. Worksheets);
 workbookDest.SaveToFile("result.xlsx");

If you have any other questions, you can leave a message in the comment area~~ I will reply you as soon as I see it~~