String Format for DateTime [C#]

// create date time 2008-03-09 16:05:07.123

DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year

String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month

String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day

String.Format("{0:h hh H HH}", dt);  // "4 04 16 16"      hour 12/24

String.Format("{0:m mm}", dt);  // "5 05"            minute

String.Format("{0:s ss}", dt);  // "7 07"            second

String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction

String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes

String.Format("{0:t tt}", dt);  // "P PM"            A.M. or P.M.

String.Format("{0:z zz zzz}", dt);  // "-6 -06 -06:00"   time zone

// date separator culture is "."

String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07"

String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07"

// month/day numbers without/with leading zeroes

String.Format("{0:M/d/yyyy}", dt);            // "3/9/2011"

String.Format("{0:MM/dd/yyyy}", dt);          // "13/09/2011"

// day/month names

String.Format("{0:ddd, MMM d, yyyy}", dt);    // "Sun, Mar 9, 2011"

String.Format("{0:dddd, MMMM d, yyyy}", dt);  // "Sunday, March 9, 2011"

// two/four digit year

String.Format("{0:MM/dd/yy}", dt);            // "13/09/11"

String.Format("{0:MM/dd/yyyy}", dt);          // "13/09/2011"

//standard format specifiers

String.Format("{0:t}", dt);  // "4:05 PM"                         ShortTime

String.Format("{0:d}", dt);  // "13/9/2011"                        ShortDate

String.Format("{0:T}", dt);  // "4:05:07 PM"                      LongTime

String.Format("{0:D}", dt);  // "Sunday, March 09, 2011"          LongDate

String.Format("{0:f}", dt);  // "Sunday, March 09, 2011 4:05 PM"  LongDate+ShortTime

String.Format("{0:F}", dt);  // "Sunday, March 09, 2011 4:05:07 PM" FullDateTime

String.Format("{0:g}", dt);  // "3/9/2011 4:05 PM"                ShortDate+ShortTime

String.Format("{0:G}", dt);  // "3/9/2011 4:05:07 PM"             ShortDate+LongTime

String.Format("{0:m}", dt);  // "March 11"                        MonthDay

String.Format("{0:y}", dt);  // "March, 2011"                     YearMonth

String.Format("{0:r}", dt);  // "Sun, 09 Mar 2011 16:05:07 GMT"   RFC1123

String.Format("{0:s}", dt);  // "2011-03-09T16:05:07"             SortableDateTime

String.Format("{0:u}", dt);  // "2011-03-09 16:05:07Z"            UniversalSortableDateTime

 

Read more »

pass table name as variable in stored procedure in mysql

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getdataforexport`(in tablename varchar(100),in tblcityname varchar(100))

BEGIN

    

      set @citycode= (select citycode from ea_citymaster where cityname=tblcityname);    

      SET @s = CONCAT('select * from ', tablename, ' where CityCode=''',@citycode,'''');

      PREPARE stmt FROM @s;

      EXECUTE stmt; 

    END$$

Read more »

export to excel in asp.net from multipal datatable and multipal sheet

protected void btnexportexcel_Click(object
sender, EventArgs e)

{

DataTable TableName1= new DataTable();

DataTable TableName2= new DataTable();

DataSet ds = new DataSet();

ds.Merge(TableName1);

ds.Merge(TableName2);

ds.Tables[0].TableName = "TableName1";

ds.Tables[1].TableName = "TableName2";

ToExcel(ds, "" + filename + ".xls",
Page.Response);

}

private static string
getWorkbookTemplate()

{

var sb = new StringBuilder(818);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@",
Environment.NewLine);

sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}",
Environment.NewLine);

sb.AppendFormat(@"
xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}",
Environment.NewLine);

sb.AppendFormat(@"
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}",
Environment.NewLine);

sb.AppendFormat(@"
xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}",
Environment.NewLine);

sb.AppendFormat(@" {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@" {0}", Environment.NewLine);

sb.Append(@"{0}\r\n");

return
sb.ToString();

}

private static string
replaceXmlChar(string input)

{

input = input.Replace("&", "&amp");

input = input.Replace("<", "<");

input = input.Replace(">", ">");

input = input.Replace("\"", """);

input = input.Replace("'", "'");

return input;

}

private static string
getCell(Type type, object
cellData)

{

var data =
(cellData is DBNull)
? "" : cellData;

if
(type.Name.Contains("Int") ||
type.Name.Contains("Double") ||
type.Name.Contains("Decimal")) return string.Format("{0}", data);

if
(type.Name.Contains("Date")
&& data.ToString() != string.Empty)

{

return string.Format("{0}", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));

}

return string.Format("{0}",
replaceXmlChar(data.ToString()));

}

private static string
getWorksheets(DataSet source)

{

var sw = new StringWriter();

if (source ==
null || source.Tables.Count == 0)

{

sw.Write("\r\n

\r\n\r\n
\r\n");

return
sw.ToString();

}

foreach (DataTable dt in
source.Tables)

{

if
(dt.Rows.Count == 0)

sw.Write(" +
replaceXmlChar(dt.TableName) + "\">\r\n

\r\n  ss:StyleID=\"s62\">\r\n
\r\n");

else

{

//write each row
data

var sheetCount
= 0;

for (int i = 0; i < dt.Rows.Count; i++)

{

if ((i %
rowLimit) == 0)

{

//add close tags
for previous sheet of the same data table

if ((i /
rowLimit) > sheetCount)

{

sw.Write("\r\n\r\n");

sheetCount = (i / rowLimit);

}

sw.Write("\r\n +
replaceXmlChar(dt.TableName) +

(((i / rowLimit) == 0) ? "" : Convert.ToString(i
/ rowLimit)) + "\">\r\n

");

//write column
name row

sw.Write("\r\n");

foreach (DataColumn dc in
dt.Columns)

sw.Write(string.Format("{0}",
replaceXmlChar(dc.ColumnName)));

sw.Write("");

}

sw.Write("\r\n");

foreach (DataColumn dc in
dt.Columns)

sw.Write(getCell(dc.DataType,
dt.Rows[i][dc.ColumnName]));

sw.Write("");

}

sw.Write("\r\n

\r\n");

}

}

return
sw.ToString();

}

public static string
GetExcelXml(DataTable dtInput, string filename)

{

var
excelTemplate = getWorkbookTemplate();

var ds = new DataSet();

ds.Tables.Add(dtInput.Copy());

var worksheets
= getWorksheets(ds);

var excelXml =
string.Format(excelTemplate, worksheets);

return excelXml;

}

public static string
GetExcelXml(DataSet dsInput, string filename)

{

var
excelTemplate = getWorkbookTemplate();

var worksheets
= getWorksheets(dsInput);

var excelXml =
string.Format(excelTemplate, worksheets);

return excelXml;

}

public static void ToExcel(DataSet dsInput, string
filename, HttpResponse response)

{

var excelXml =
GetExcelXml(dsInput, filename);

response.Clear();

response.Buffer = true;

response.BufferOutput = true;

//
response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

response.Charset = "UTF-8";

response.ContentEncoding =
System.Text.Encoding.UTF8;

//response.ContentType
= "application/vnd.ms-excel";

response.AppendHeader("Content-Type", "application/ms-excel");

response.AppendHeader("Content-disposition", "attachment; filename=" + filename);

response.Write(excelXml);

response.Flush();

response.End();

}

public static void ToExcel(DataTable dtInput, string
filename, HttpResponse response)

{

var ds = new DataSet();

ds.Tables.Add(dtInput.Copy());

ToExcel(ds, filename,
response);

}

Read more »