generic list using search

Question

2.50/5 (2 votes)

See more:

internal static void GenerateExcel(DataTable dt)
{
Microsoft.Office.Interop.Excel.Application objApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks objBooks = objApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook objBook = objBooks.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet objSheet = null;

if (dt.Rows.Count > 0)
{
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
((Microsoft.Office.Interop.Excel.Worksheet)objBook.Sheets[1]).Select(Missing.Value);

int row = 0;
//Headers
objSheet.Cells[row + 1, 1] = "CC_CODE";
objSheet.Cells[row + 1, 2] = "DAY";
objSheet.Cells[row + 1, 3] = "MONTH";
objSheet.Cells[row + 1, 4] = "YEAR";
objSheet.Cells[row + 1, 5] = "SHIFT";
objSheet.Cells[row + 1, 6] = "SCODE";
objSheet.Cells[row + 1, 7] = "MILKTYPE";
objSheet.Cells[row + 1, 8] = "CAN";
objSheet.Cells[row + 1, 9] = "KG";
objSheet.Cells[row + 1, 10] = "LITER";
objSheet.Cells[row + 1, 11] = "LR";
objSheet.Cells[row + 1, 12] = "FAT";
objSheet.Cells[row + 1, 13] = "SNF";
objSheet.Cells[row + 1, 14] = "SOUR STATUS";
objSheet.Cells[row + 1, 14].EntireRow.Font.Bold = true;
objSheet.Columns[16].AutoFit();
foreach (DataRow dr in dt.Rows)
{
row++;
for (int col = 0; col < dt.Columns.Count; col++)
{
objSheet.Cells[row + 1, col + 1] = dr[col].ToString();
objSheet.Cells[row + 1, 1] = Settings.Instance.UnionCode;
objSheet.Cells[row + 1, 2] = Convert.ToDateTime(dr["Date"]).Day;
objSheet.Cells[row + 1, 3] = string.Format("{0:MM}", Convert.ToDateTime(dr["Date"]));
objSheet.Cells[row + 1, 4] = Convert.ToDateTime(dr["Date"]).Year;
objSheet.Cells[row + 1, 5] = dr["Shift"].ToString();
objSheet.Cells[row + 1, 6] = dr["SocNo"].ToString();
objSheet.Cells[row + 1, 7] = "C";
objSheet.Cells[row + 1, 8] = dr["CAN"].ToString();
objSheet.Cells[row + 1, 9] = " ";
objSheet.Cells[row + 1, 10] = dr["Liter"].ToString();
objSheet.Range["J2", "J" + row + 1].NumberFormat = "####.0";
objSheet.Range["L2", "L" + row + 1].NumberFormat = "#.0";
objSheet.Range["M2", "M" + row + 1].NumberFormat = "#.0";
objSheet.Cells[row + 1, 11] = "";
objSheet.Cells[row + 1, 12] = dr["Fat"].ToString();
objSheet.Cells[row + 1, 13] = dr["Snf"].ToString();
objSheet.Cells[row + 1, 14] = "";
}
}

objBook.Close(true, @"D:\12586.xlsx", Missing.Value);
objBooks.Close();
objApp.Quit();

}
}

Posted 2-Apr-14 20:42pm

satheeshkumar chinnadurai

Updated 22-Apr-14 2:37am

v3

Add a Solution

Comments

lukeer 3-Apr-14 3:22am

I don't quite get your problem.
Searching for an element in a list, you can iterate over all elements in the list and compare those properties that you're interested in.
Where are you stuck?
(Improve your question[^] accordingly)

Maciej Los 3-Apr-14 17:18pm

Yeah, it's not clear.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

aricode · Answer 1 · 2014-04-07T06:03:00

Solution 1

Hi, if you want to perform a pattern search on list of CaseManagement based on certain properties LINQ is a good choice..
something like below..

XML

static List<CaseManagement> ListCaseDetails()
        {
            return new List<CaseManagement>()
            { new CaseManagement() { CustomerName = "Customer1", CaseTitle="CaseTitle111"},
                new CaseManagement() {CustomerName = "Customer22", CaseTitle="CaseTitle22" },
                new CaseManagement() { CustomerName = "Customer333", CaseTitle="CaseTitle3"} };
        }
        static void Search()
        {
            Regex reg = new Regex(@"^[A-Za-z]*\d{1}$", RegexOptions.IgnoreCase);
            var x = ListCaseDetails().Where(p => reg.IsMatch(p.CustomerName) == true || reg.IsMatch(p.CaseTitle) == true).ToList();

        }

This will result in {Customer1, CaseTitle111} and {Customer333, CaseTitle3} as output

Posted 7-Apr-14 6:03am

aricode

Comments

satheeshkumar chinnadurai 22-Apr-14 8:38am

internal static void GenerateExcel(DataTable dt)
{
Microsoft.Office.Interop.Excel.Application objApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks objBooks = objApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook objBook = objBooks.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet objSheet = null;

Microsoft.Office.Interop.Excel.Range range = null;

if (dt.Rows.Count > 0)
{
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
((Microsoft.Office.Interop.Excel.Worksheet)objBook.Sheets[1]).Select(Missing.Value);

int row = 0;
//Headers
objSheet.Cells[row + 1, 13] = Settings.Instance.UnionName;
objSheet.get_Range("A1", "M1").Merge(Type.Missing);
row = row + 1;

objSheet.Cells[row + 1, 3] = "Society Details";
objSheet.get_Range("A2", "C2").Merge(true);

objSheet.Cells[row + 1, 7] = "Morning";
objSheet.get_Range("D2", "H2").Merge(true);
objSheet.Cells[row + 1, 10] = "Evening";
objSheet.get_Range("I2", "M2").Merge(true);
row = row + 1;
objSheet.Cells[row + 1, 1] = "SNo";
objSheet.Cells[row + 1, 2] = "SocCode";
objSheet.Cells[row + 1, 3] = "SocName";
objSheet.Cells[row + 1, 4] = "Sample";
objSheet.Cells[row + 1, 5] = "Can";
objSheet.Cells[row + 1, 6] = "Liter";
objSheet.Cells[row + 1, 7] = "FAT";
objSheet.Cells[row + 1, 8] = "SNF";
objSheet.Cells[row + 1, 9] = "Sample";
objSheet.Cells[row + 1, 10] = "Can";
objSheet.Cells[row + 1, 11] = "Liter";
objSheet.Cells[row + 1, 12] = "FAT";
objSheet.Cells[row + 1, 13] = "SNF";
foreach (DataRow dr in dt.Rows)
{
row++;
for (int col = 0; col < dt.Columns.Count; col++)
{
if (int.Parse(dr["Shift"].ToString()) == 1)
{
objSheet.Cells[row + 1, 1] = dt.Columns.Count;
objSheet.Cells[row + 1, 2] = dr["SocNo"].ToString();
objSheet.Cells[row + 1, 5] = dr["CAN"].ToString();
objSheet.Cells[row + 1, 6] = dr["FAT"].ToString();
objSheet.Cells[row + 1, 7] = dr["snf"].ToString();

}

}
}

objBook.Close(true, @"D:\DMR.xlsx", Missing.Value);
objBooks.Close();

}
}
}

satheeshkumar chinnadurai 22-Apr-14 8:38am

internal sealed class Settings
{
private static Settings _instance = null;
private static object _lock = new object();
private int _UnionCode;
public int UnionCode { get { return _UnionCode; } }
private string _UnionName;
private string _SocDetails;
public string UnionName { get { return _UnionName; } }
public string SocDetails { get { return _SocDetails; } }
private Settings() // Made default constructor as private
{
_UnionCode = 20;
_UnionName = "Sa";
_SocDetails = "Sa";
}
public static Settings Instance
{
get
{
lock (_lock)
{
_instance = _instance ?? new Settings();
return _instance;
}
}
}
}

satheeshkumar chinnadurai 23-Apr-14 8:06am

Microsoft.Office.Interop.Excel.Range Sno = objSheet.get_Range("A4:B4", System.Type.Missing);
Sno.EntireColumn.ColumnWidth = 3;

Microsoft.Office.Interop.Excel.Range eSocCode = objSheet.get_Range("B4:C4", System.Type.Missing);
eSocCode.EntireColumn.ColumnWidth = 8;

Microsoft.Office.Interop.Excel.Range eSocName = objSheet.get_Range("C4:D4", System.Type.Missing);
eSocName.EntireColumn.ColumnWidth = 20;

Microsoft.Office.Interop.Excel.Range emSample = objSheet.get_Range("D4:E4", System.Type.Missing);
emSample.EntireColumn.ColumnWidth = 6;

Microsoft.Office.Interop.Excel.Range emCan = objSheet.get_Range("E4:F4", System.Type.Missing);
emCan.EntireColumn.ColumnWidth = 4;

Microsoft.Office.Interop.Excel.Range emLiter = objSheet.get_Range("F4:G4", System.Type.Missing);
emLiter.EntireColumn.ColumnWidth = 7;

Microsoft.Office.Interop.Excel.Range emFat = objSheet.get_Range("G4:H4", System.Type.Missing);
emFat.EntireColumn.ColumnWidth = 4;

Microsoft.Office.Interop.Excel.Range emSnf = objSheet.get_Range("H4:I4", System.Type.Missing);
emSnf.EntireColumn.ColumnWidth = 3;

Microsoft.Office.Interop.Excel.Range eeSample = objSheet.get_Range("I4:J4", System.Type.Missing);
eeSample.EntireColumn.ColumnWidth = 6;

Microsoft.Office.Interop.Excel.Range eeCan = objSheet.get_Range("J4:K4", System.Type.Missing);
eeCan.EntireColumn.ColumnWidth = 4;

Microsoft.Office.Interop.Excel.Range eeLiter = objSheet.get_Range("K4:L4", System.Type.Missing);
eeLiter.EntireColumn.ColumnWidth = 7;

Microsoft.Office.Interop.Excel.Range eeFat = objSheet.get_Range("L4:M4", System.Type.Missing);
eeFat.EntireColumn.ColumnWidth = 4;

Microsoft.Office.Interop.Excel.Range eeSnf = objSheet.get_Range("M4:N4", System.Type.Missing);
eeSnf.EntireColumn.ColumnWidth = 3;

satheeshkumar chinnadurai 23-Apr-14 8:06am

objSheet.UsedRange.Borders.Color = System.Drawing.Color.Black;

satheeshkumar chinnadurai 23-Apr-14 8:13am

try
{
MailMessage mail = new MailMessage();
mail.To.Add("xxx@gmail.com");
mail.To.Add("xxx@gmail.com");
mail.From = new MailAddress("x@gmail.com");
mail.Subject = "TestMail";
string Body = "Vel";
mail.IsBodyHtml = true;

SmtpClient smtp = new SmtpClient("localhost", 25);

smtp.Host = "smtp.gmail.com"; //Or Your SMTP Server Address
smtp.Credentials = new System.Net.NetworkCredential("xxx@gmail.com", "xxxxxxx");

smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.EnableSsl = true;

Attachment data = new Attachment(@"D:\DMR.xlsx");
mail.Attachments.Add(data);
smtp.Send(mail);
//MessageBox.Show("Mail Send");
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
}