We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Create a worksheet by selecting/filtering data in another worksheet

I’ll like to create a worksheet B by selecting only data in work sheet A where values in the first column of A contain a certain value (say a value 10).

Hi,


Thank you for contacting support. You can apply an auto filter to a range of cells in the worksheet, refresh it to get only the filtered data, and then copy the range of filtered data to the destination worksheet worksheet by specifying the destination range. We have attached the source excel file and output excel file for your kind reference.

[.NET, C#]
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>Workbook wb = <span class=“kwrd” style=“color: rgb(0, 0, 255); font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>new<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”> Workbook(“source.xlsx”);
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>Worksheet dstSheet = wb.Worksheets.Add(“Dest”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>Worksheet ws = wb.Worksheets[0];<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.Range = “A1:H1”;<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.AddFilter(2, “1”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.AddFilter(2, “4”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.AddFilter(2, “6”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.AddFilter(2, “8”);<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.Refresh();<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>Range rng = ws.Cells.CreateRange(“A1”, “J11”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>Range dstRng = dstSheet.Cells.CreateRange(“A1”, “J11”);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>PasteOptions opts = <span class=“kwrd” style=“color: rgb(0, 0, 255); font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>new<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”> PasteOptions();<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>opts.OnlyVisibleCells = <span class=“kwrd” style=“color: rgb(0, 0, 255); font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>true<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>;<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>opts.PasteType = PasteType.Values;<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>dstRng.Copy(rng, opts);<br style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”><span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>wb.Save(“output.xlsx”);
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 255);”>

Hello Imran



Thanks, but the solution is not quite what I am looking for. I simply want to select the data in worksheet A based on a criterion (e.g. the value of Column A being 10) and use the selected data to create a new worksheet. See the attached “Source data” and “Filtered data” illustration.

Hi,


Thank you for the inquiry. Please modify the code as below:

.NET, C#
<span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre; background-color: rgb(255, 255, 255);”>Worksheet ws = wb.Worksheets[0];<br style=“color: rgb(51, 51, 51); font-size: small; background-color: rgb(255, 255, 255); font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre;”><span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.Range = “A1:H1”;
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre;”>// here 0 is the column index <br style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre;”><span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre;”>ws.AutoFilter.AddFilter(0, “10”);<span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre; background-color: rgb(255, 255, 255);”>
<span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre; background-color: rgb(255, 255, 255);”>ws.AutoFilter.Refresh();
<span style=“color: rgb(51, 51, 51); font-size: small; font-family: “Courier New”, Consolas, Courier, monospace; white-space: pre; background-color: rgb(255, 255, 255);”>