Comments by Aksh@169 (Top 22 by date)

Can u pls tell me how to achieve what u have written in ur former idea if my code does the following:




public void automate()
{

string filepath,filename, fileExcel,renamedfile;


string ReportCount = "select count(rs.contactID) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
string UserCount = "select count(distinct(rs.contactID)) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
Random nRandom = new Random(DateTime.Now.Millisecond);

//Create a random file name.
fileExcel = "t" + nRandom.Next().ToString() + ".xls";
filepath = "C:\\test";
filename = filepath + "\\" + "test.xlsx";
renamedfile = System.IO.Path.GetFileNameWithoutExtension(filename);
renamedfile = System.IO.Path.GetPathRoot(filename) + "test\\" + renamedfile + '_' + DateTime.Now.ToString("ddMMyyyy hhmm") + System.IO.Path.GetExtension(filename);
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.AppSettings["Connection"];
SqlCommand cmd = new SqlCommand("select rs.contactID,SurveyType,ModuleName,LogDate from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546) order by LogDate ", conn);
SqlCommand cmd1 = new SqlCommand(ReportCount, conn);
SqlCommand cmd2 = new SqlCommand(UserCount, conn);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmd);
DataSet d = new DataSet();
mySqlDataAdapter.Fill(d, "dataset");
conn.Open();

string reports = Convert.ToString(cmd1.ExecuteScalar());
string users = Convert.ToString(cmd2.ExecuteScalar());

int sheetIndex = 0;

// Copy each DataTable
foreach (System.Data.DataTable dt in d.Tables)
{

// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

// Copy the column names to the first row of the object array

for (int col = 0; col < dt.Columns.Count; col++)
{
// if(dt.Columns[col].ColumnName).
rawData[0, col] = dt.Columns[col].ColumnName;

}

// Copy the values to the object array

for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}

// Calculate the final column letter

string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dt.Columns.Co