Prev: How to use two parameters in input of popup Variables - Gary's Student
Next: Advanced filter
From: Kelkars on 3 Feb 2010 01:17
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.
 | 
Pages: 1
Prev: How to use two parameters in input of popup Variables - Gary's Student
Next: Advanced filter