Adjust text to fit within an Excel Cell using C#

K

Kelkars

I am developing a console application in which I have exported data from
dataset to an Excel. I need to adjust the text to fit within the cells
without using wordwrap. As my requirement is to achieve the data in every
column in a single row. where my data in each column keeps varying.
Below is the method code used to generate the excel sheet report.


private static void GenerateWorkSheet(XlsDocument doc, DataTable dt, string
workSheetName)
{
Worksheet sheet = doc.Workbook.Worksheets.Add(workSheetName);

if (dt != null)
{
string[] columns2Delete = { "Project", "Title",
"WorkProduct", "Reference", "LinkTitleNoMenu", "LinkTitle", "ID",
"ContentType", "Modified", "Created", "_UIVersionString", "Attachments",
"DocIcon", "_ModerationComments" };
foreach (string s in columns2Delete)
{
if (dt.Columns.Contains(s))
{
dt.Columns.Remove(s);
dt.AcceptChanges();
}
}
for (int dcCount = 1; dcCount <= dt.Columns.Count; dcCount++)
{
String columnName = dt.Columns[dcCount -
1].ColumnName.Replace("_x0020_", "");
Cell cell = sheet.Cells.Add(1, dcCount, columnName);
cell.Font.Weight = FontWeight.Bold;
cell.UseBackground = true;
cell.Font.ColorIndex = 1;
cell.PatternColor = Colors.Default36;
cell.Pattern = 1;
cell.PatternBackgroundColor =
Colors.SystemAutomaticColorForChartBorderLines;
cell.BottomLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.RightLineStyle = 1;



}
int tempOffset = 2;
bool changeColor = true;
for (int drCount = 0; drCount < dt.Rows.Count; drCount++)
{
for (int drItemCount = 0; drItemCount <
dt.Rows[drCount].ItemArray.Length; drItemCount++)
{
if ((dt.Rows[drCount].ItemArray[drItemCount] !=
DBNull.Value) && (dt.Rows[drCount].ItemArray[drItemCount] != null))
{
string _value =
dt.Rows[drCount].ItemArray[drItemCount].ToString();
if (_value.ToLower().Contains("cts\\"))
{
_value = GetUserName(_value);
}
if (_value.ToLower().Contains("<div>"))
{
_value = _value.Replace("<div>", "");
}
if (_value.ToLower().Contains("</div>"))
{
_value = _value.Replace("</div>", "");
}
if (_value.ToLower().Contains(" "))
{
_value = _value.Replace(" ", "");
}


Cell cell = sheet.Cells.Add(tempOffset,
drItemCount + 1, _value);
cell.UseBackground = true;
//cell.Font.ColorIndex = 1;
cell.Pattern = 1;
cell.PatternBackgroundColor =
Colors.SystemAutomaticColorForChartBorderLines;
cell.BottomLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.RightLineStyle = 1;

if (changeColor)
{
cell.PatternColor = Colors.Default1F;
}
else
{
cell.PatternColor = Colors.White;
}
}
else
{
Cell cell = sheet.Cells.Add(tempOffset,
drItemCount + 1, String.Empty);
cell.UseBackground = true;
//cell.Font.ColorIndex = 1;
cell.Pattern = 1;
cell.PatternBackgroundColor =
Colors.SystemAutomaticColorForChartBorderLines;
cell.BottomLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.RightLineStyle = 1;
//cell.HorizontalAlignment =
HorizontalAlignments.Justified;
//cell.VerticalAlignment =
VerticalAlignments.Justified;

if (changeColor)
{
cell.PatternColor = Colors.Default1F;
}
else
{
cell.PatternColor = Colors.White;
}
}
}
tempOffset += 1;
changeColor = !changeColor;
}
}
}


Kindly share some solution.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top