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.
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.