Creating an Excel Report without using Microsoft Office Interop

It was just recent, when the company decided to removed and select only a few user who are or rather should have an Office suite on their local computer. There was nothing wrong with it, but the only issue is that the current system that they are using are dependent on MS interop.

It was a pain on my side since the users that was not selected to have an office suite on their unit are also generating a report which are in .xls format. I made some research on some alternative ways that I can implement on the system and I came across this awsome library which is called EPPlus.

EPPlus is easy to use and rewriting my existing .xls report is a no sweat at all!. Here are also some of the site that I found helpfull.

1. http://www.codeproject.com/Articles/680421/Create-Read-Edit-Advance-Excel-Report-in
2. http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx
3. https://www.paragon-inc.com/resources/blogs-posts/easy_excel_interaction_pt5

As always, a big thanks to the persons that are involved in creating EPPlus!.

c#: Datagridview with Page number

I was tired of displaying a record in a grid view with only has a Next and Previous button on it, co'z I feel like I am only limited in those button, so I decided to make a something like "Paging".

First, we have to specify the design. Add a datagridview and seven Buttons on the form.
And change each button to the following names

"Button1" will be change to "btnPrevious"
"Button2" will be change to "btnOne"
"Button3" will be change to "btnTwo"
"Button4" will be change to "btnThree"
"Button5" will be change to "btnFour"
"Button6" will be change to "btnFive"
"Button7" will be change to "btnNext"

The UI should somehow look  like this.

Double click the form and declare this code inside the form class.

 int totalRecord;
        int rowToDisplay = 10;

        DataTable DataStorage = new DataTable();

       

On the form load, add some sample data on the declared datatable and get the total count of records created.

private void Form1_Load(object sender, EventArgs e)
{
            // Create a column for DataStorage
            DataStorage.Columns.Add("Col1", typeof(string));
            DataStorage.Columns.Add("Col2", typeof(string));
            DataStorage.Columns.Add("Col3", typeof(string));
            DataStorage.Columns.Add("Col4", typeof(string));

            for (int i = 0; i <= 5554; i++)
            {
                // add a record
                DataStorage.Rows.Add(i, "A" + i, "B" + i, "C" + i);
            }

            totalRecord = DataStorage.Rows.Count;

            BindTheResult(0);

}

Create a method that will display the result in datagridview. In this method, it will calculate the start index on which the created datatable will be sought.

private void BindTheResult(int startFrom)
        {

            var start = (startFrom * rowToDisplay);// identify index to start

            DataStorage.DefaultView.RowFilter = string.Format("Col1>= {0} AND Col1<={1}", start, rowToDisplay + start);
            dataGridView1.DataSource = DataStorage.DefaultView;

            PreparePaging(startFrom);
        }

The method below will handle the buttons display and some events. This method will also compute for the total page according to the given number of rows to display.

private void PreparePaging(int startFrom)
        {

            var totalPage = 0;

            if ((totalRecord % rowToDisplay) > 0)
                totalPage = (totalRecord / rowToDisplay) + 1;
            else
                totalPage = (totalRecord / rowToDisplay);
            
            var x = 1;
            var y = 0;

            for (y = 1; y <= totalPage; y += 5)// step 5= where 5 represents the button one to five on the UI.
            {
                if (startFrom + 1 >= y && startFrom + 1 <= y + 4)
                    break;
            }

            startFrom = y;
            
            // make sure to clear the text and disable the button
            btnPrevious.Enabled = startFrom > 1;
            btnOne.Enabled = false;
            btnTwo.Enabled = false;
            btnThree.Enabled = false;
            btnFour.Enabled = false;
            btnFive.Enabled = false;
            btnNext.Enabled = false;

            btnOne.Text = string.Empty;
            btnTwo.Text = string.Empty;
            btnThree.Text = string.Empty;
            btnFour.Text = string.Empty;
            btnFive.Text = string.Empty;

            if (y > 1)
            {
                btnPrevious.Enabled = true;
                btnPrevious.Tag = y - 5;
            }

            for (int i = startFrom; i <= totalPage; i++)
            {
                if (x > 5)
                {
                    btnNext.Enabled = true;
                    btnNext.Tag = i.ToString();
                    break;
                }
                else
                {
                    if (x == 1)
                    {
                        btnOne.Tag = i.ToString();
                        btnOne.Text = i.ToString();
                        btnOne.Enabled = true;
                    }
                    if (x == 2)
                    {
                        btnTwo.Tag = i.ToString();
                        btnTwo.Text = i.ToString();
                        btnTwo.Enabled = true;
                    }
                    if (x == 3)
                    {
                        btnThree.Tag = i.ToString();
                        btnThree.Text = i.ToString();
                        btnThree.Enabled = true;
                    }
                    if (x == 4)
                    {
                        btnFour.Tag = i.ToString();
                        btnFour.Text = i.ToString();
                        btnFour.Enabled = true;
                    }
                    if (x == 5)
                    {
                        btnFive.Tag = i.ToString();
                        btnFive.Text = i.ToString();
                        btnFive.Enabled = true;
                    }
                }

                x += 1;
            }

        }

        

Once everything is completed, its time to handle each buttons events. Note that button one - five events are handled in BtnOne_Click method.

private void btnPrevious_Click(object sender, EventArgs e)
        {
            var start = btnPrevious.Tag.ToString() == "1" ? 0 : Convert.ToInt32(btnPrevious.Tag) - 1;
            BindTheResult(Convert.ToInt32(start));
        }

        private void btnOne_Click(object sender, EventArgs e)
        {
            var btn = (Button)sender;

            BindTheResult(Convert.ToInt32(btn.Tag) - 1);
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            BindTheResult(Convert.ToInt32(btnNext.Tag) - 1);
            btnPrevious.Enabled = true;
        }

 


If there are some bugs or errors found, kindly comment below.

vb: Line by line printing

I just wanna share this piece of code that will help some of my fellow programmer's out there that are having a problem in printing something line by line.

Mostly, this kind of printing is done on printer that uses a carbon/ribbon printer like Epson Lx300+. Also, if you want the printer to stop right after the last character then this code will help you a lot.

This will also use the RawPrinterHelp class found at https://support.microsoft.com/en-us/kb/322090 and some ASCII code to send command on the printer. The ASCII command that I used in this code is for Epson printer.

 Dim FN As Long = FreeFile() ' get the next id to use

 FileOpen(FN, "C:\report.prn", OpenMode.Output)

PrintLine(FN, Chr(27) + Chr(64))  ' Initialize Printer

PrintLine(FN, TAB(3), "-------------------- -------------------------- ------")
PrintLine(FN, TAB(3), "         Item              Description            Qty")
PrintLine(FN, TAB(3), "-------------------- -------------------------- ------")

PrintLine(FN) ' empty row

PrintLine(FN,Tab(3),"Item1",Tab(24),"Item1 Description",Tab(51),12) 
PrintLine(FN,Tab(3),"Item2",Tab(24),"Item2 Description",Tab(51),10) 
PrintLine(FN,Tab(3),"Item3",Tab(24),"Item3 Description",Tab(51),35) 

PrintLine(FN, chr(12)) ' Eject
FileClose(FN)

The above code will create the report and save it at C directory with "report.prn" filename. Open this report using notepad application.

It's also much better to print directly the report right after it has been made, the below code will be use for this.

To print the report created:

Dim SR As New StreamReader("C:\report.prn", System.Text.Encoding.Default)
Dim dataRead As [String] = SR.ReadToEnd()
SR.Close()
SR.Dispose()
RawPrinterHelper.SendStringToPrinter(printer name, dataRead )

Hide DataGridview Checkbox in Specific Cells

I just wanna share this piece of code which will help someone out there that is having a problem hiding a specific checkbox cell in a datagridview.

This code may not be the best solution out there. But, this piece of code help me achieve my requirements.

With Datagridview1.Rows(rowIndex)
            .Cells(CheckBoxColName) = New DataGridViewTextBoxCell
            .Cells(CheckBoxColName).Value = String.Empty
 End With

Sample:

Let me know if you have the most best solution by commenting below.

Vb: Custom Column Header in Datagridview

I was browsing the web when I came across with this site at http://gauravsofts.blogspot.com/2010/02/print-multiple-layered-column-header-in.html. It has a good sample of codes about the multi-layered datagridview and after reading the post, I decided to make use of it.

I made some changes that fit to my requirements.

Sample Output

Codes: 

 Private Sub AddGridViews()  
     Dim subColumnHeader As String() = {"Fabric", "XS", "S", "M", "L", "XL"}  
     Dim dgView As DataGridView  
     FlowLayoutPanel1.SuspendLayout()  
     For i As Integer = 1 To 5  
       dgView = New DataGridView  
       With dgView  
         .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill  
         .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing  
         .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter  
         .Size = New Size(307, 170)  
         .ColumnHeadersHeight = 40  
         For Each col As String In subColumnHeader  
           .Columns.Add("col" & col, col)  
         Next  
         .Rows.Add(3)  
         .Tag = "Fabric " & i ' holds the header of the grid  
       End With  
       AddHandler dgView.CellPainting, AddressOf dataGridView1_CellPainting  
       AddHandler dgView.Paint, AddressOf dataGridView1_Paint  
       FlowLayoutPanel1.Controls.Add(dgView)  
     Next  
     FlowLayoutPanel1.ResumeLayout()  
   End Sub  
  Private Sub dataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs)  
     If e.RowIndex = -1 AndAlso e.ColumnIndex > -1 Then  
       e.PaintBackground(e.CellBounds, False)  
       Dim r2 As Rectangle = e.CellBounds  
       r2.Y += e.CellBounds.Height / 2  
       r2.Height = e.CellBounds.Height / 2  
       e.PaintContent(r2)  
       e.Handled = True  
     End If  
   End Sub  
   Private Sub dataGridView1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs)  
     ' Data for the "merged" header cells  
     Dim obj = TryCast(sender, DataGridView)  
     ' Get the column header cell bounds  
     Dim r1 As Rectangle = obj.GetCellDisplayRectangle(0, -1, True)  
     r1.X += 1  
     r1.Y += 1  
     r1.Width = r1.Width * 6 - 2  
     r1.Height = r1.Height / 2 - 2  
     e.Graphics.FillRectangle(Brushes.Beige, r1)  
     e.Graphics.DrawLine(Pens.BurlyWood, r1.X, r1.Bottom, r1.Right, r1.Bottom)  
     Using format As StringFormat = New StringFormat()  
       Using br As SolidBrush = New SolidBrush(obj.ColumnHeadersDefaultCellStyle.ForeColor)  
         format.Alignment = StringAlignment.Center  
         format.LineAlignment = StringAlignment.Center  
         e.Graphics.DrawString(obj.Tag.ToString, obj.ColumnHeadersDefaultCellStyle.Font, _  
                    br, r1, format)  
       End Using  
     End Using  
   End Sub  
  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
     AddGridViews()  
   End Sub  

Hope this will help someone out there.

Thanks to Gaurav Khanna.