How to Find Blank cell and replace with other letter

Aspose.Cells Product Family
1

I am working on new file so excel attachment is not required. Attached the code in txt file. Same code is given below also,



private void button2_Click(object sender, EventArgs e)

{

MemoryStream memoryStream = new MemoryStream();

string filePath = @“D:\Hetal\Test.xlsx”;



//var stream = File.OpenWrite(filePath);

Workbook workbook = new Workbook();

int i = workbook.Worksheets.Add();

Worksheet worksheet = workbook.Worksheets[i];



DataTable dt = new DataTable();

dt.Columns.Add(“NumericField”, typeof(System.Double));

dt.Columns.Add(“StringField”, typeof(System.String));

dt.Columns.Add(“Account”, typeof(System.String));

dt.Columns.Add(“DOB”, typeof(System.String));

DataRow dr1 = dt.NewRow();

//dr1[0] = “”;

dr1[1] = “Multi”;

dr1[2] = “467674”;

dr1[3] = “10/12/1980”;

dt.Rows.Add(dr1);

for(i=0; i
{

DataRow dr = dt.NewRow();

dr[0] = i;

dr[1] = i;

dr[2] = “ADGB” + i.ToString();

dr[3] = DateTime.Now.AddDays(i).ToShortDateString();

dt.Rows.Add(dr);

}

DataRow dr2 = dt.NewRow();

dr2[0] = “-4”;

dr2[1] = “-4”;

dr2[2] = “5K020E95”;

dt.Rows.Add(dr2);

worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “m/d/yyyy h:mm”, true);



Aspose.Cells.Range oRange = worksheet.Cells.CreateRange(1, 0, dt.Rows.Count, 2);

Aspose.Cells.Style oStyle = worksheet.Cells[oRange.FirstRow, oRange.FirstColumn].GetStyle();

oStyle.Custom = “0.00%;-0.00%”;

Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();

flag.All = true;



CellArea area = CellArea.CreateCellArea(“A1”, “D6”);

FindOptions opts = new FindOptions();

opts.LookInType = LookInType.Values;

opts.LookAtType = LookAtType.Contains;

opts.SetRange(area);

Cell cell = null;

do

{

cell = worksheet.Cells.Find(string.Empty, cell, opts);

if (cell == null)

break;

cell.PutValue("-");

} while (true);



oRange.ApplyStyle(oStyle, flag);



workbook.Save(memoryStream, new OoxmlSaveOptions(SaveFormat.Xlsx));



using (FileStream oFileStream =

new FileStream(filePath, System.IO.FileMode.Create, FileAccess.ReadWrite))

{

memoryStream.WriteTo(oFileStream);

oFileStream.Close();

}



this.Close();

}

2

Hi Hetal,


Thank you for contacting Aspose support.

Please try the following piece of code instead of the find operation from your code.

C#

CellArea area = CellArea.CreateCellArea(“A1”, “D6”);
for (int r = area.StartRow; r < area.EndRow + 1; r++)
{
for (int c = area.StartColumn; c < area.EndColumn + 1; c++)
{
Cell cell = worksheet.Cells[r, c];
if (cell.Type == CellValueType.IsNull)
{
worksheet.Cells[r, c].PutValue("-");
}
}
}
3

hi,
thanks for the quick reply.

Yes this I know and it works but my report contains millions of records so don’t want performance issue because of traversing through each cell.

Find will be quicker as compared to each cell by cell search.

Pls advice.

Thanks

4

Hi again,


Thank you for the clarification. Please try the following snippet for find operation on your side.

C#

CellArea area = CellArea.CreateCellArea(“A1”, “D6”);
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OriginalValues;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);
Cell cell = null;
do
{
cell = worksheet.Cells.Find(string.Empty, cell, opts);
if (cell == null)
break;
cell.PutValue("-");
} while (true);
5

hi,
Thanks for reply again but I did try this. I have that code commented in my code given in the beginning. This does not work even after I change from LookAtType.Contains to LookAtType.EntireContent.

Does this work at your end?

Thanks,
Hetal

6

Hi again,


Yes, I am able to get the correct results with the snippet provided in my previous response. Attached is the resultant spreadsheet for your reference as well as the complete code as follow. Please give it a try against Aspose.Cells for .NET 9.0.9 (latest at the moment).

C#

Workbook workbook = new Workbook();
int i = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[i];
DataTable dt = new DataTable();
dt.Columns.Add(“NumericField”, typeof(System.Double));
dt.Columns.Add(“StringField”, typeof(System.String));
dt.Columns.Add(“Account”, typeof(System.String));
dt.Columns.Add(“DOB”, typeof(System.String));
DataRow dr1 = dt.NewRow();
//dr1[0] = null;
dr1[1] = “Multi”;
dr1[2] = “467674”;
dr1[3] = “10/12/1980”;
dt.Rows.Add(dr1);
for (i = 0; i < 3; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = i;
dr[2] = “ADGB” + i.ToString();
dr[3] = DateTime.Now.AddDays(i).ToShortDateString();
dt.Rows.Add(dr);
}
DataRow dr2 = dt.NewRow();
dr2[0] = “-4”;
dr2[1] = “-4”;
dr2[2] = “5K020E95”;
//dr2[3] = “”;
dt.Rows.Add(dr2);
worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “m/d/yyyy”, true);
Aspose.Cells.Range oRange = worksheet.Cells.CreateRange(1, 0, dt.Rows.Count, 2);
Aspose.Cells.Style oStyle = worksheet.Cells[oRange.FirstRow, oRange.FirstColumn].GetStyle();
oStyle.Custom = “0.00%;-0.00%”;
Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.All = true;

CellArea area = CellArea.CreateCellArea(“A1”, “D6”);
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OriginalValues;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);
Cell cell = null;
do
{
cell = worksheet.Cells.Find(string.Empty, cell, opts);
if (cell == null)
break;
cell.PutValue("-");
} while (true);

oRange.ApplyStyle(oStyle, flag);
workbook.Save(memoryStream, new OoxmlSaveOptions(SaveFormat.Xlsx));
using (FileStream oFileStream =
new FileStream(dir + “output.xlsx”, System.IO.FileMode.Create, FileAccess.ReadWrite))
{
memoryStream.WriteTo(oFileStream);
oFileStream.Close();
}
7

hi,
I am not getting OriginalValues as one of the option in the list. is this new value to the list?
I am using 7.5.3.0 version.

Thanks,
Hetal

8

Hi again,


As you are using quite old revision of the API so it is possible that LookInType.OriginalValues may not be available in 7.5.3.
9

ok,
I cannot change the version so easily here it will take lot of time. So any other way to avoid reading each and every cell ?

Thanks,
Hetal

10

Hi again,


I am afraid, it appears that you have to either upgrade your project to use the most recent revisions of the API or opt the approach shared here as you are missing a key feature (for this scenario) in your current version of the API. By the way, upgrading the project to recent revisions of Aspose.Cells for .NET will not be so hard as we are maintaining Public API Change Section since v8.0.0. Moreover, you can always contact us in case you need our help or face any difficulty during upgrade process.

11

ok Thanks,

I will download the new version and work on it.



You can close this post.



Thanks for the help,

Hetal

12

Hi Hetal,


Sure. Please feel free to contact us back in case you need our further assistance with Aspose APIs.