excel integration with c# .net

Question

I needs to create shipping lable based on the excel template at run time using the c#.

Right now I have working system, where I am replacing the fields name in excel template with database value and sending it for printing.

But It is creating space if value is null in database. I don't want that space. In a single excel row, there are more than 2 cells needs to be replaced so if value is not present then I needs to delete the cell only(as I can't delete row) and shift its just below cell to that position or merge it with below cell.

If you have any better way to generate shipping lable based on excel template then let me know that.Thanks

                Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.ApplicationClass();

            //string path = AppDomain.CurrentDomain.BaseDirectory + @printLabelName;
            string fileLocation = "";
            DataTable dtPath = clsData.getFileLocations();
            if (dtPath.Rows.Count != 0)
            {
                //ctbExport.Text = dt.Rows[0]["exportFileLocation"].ToString();
                fileLocation = dtPath.Rows[0]["printTemplateLocation"].ToString() + "\\";
            }

            String[] orderList = ctbOrderNo.Text.Split(' ');
            for (int i = 0; i < orderList.LongLength; i++)
            {
                string strOrderNo = orderList[i];

                int val = clsData.updOrderDataBulkPrint(Convert.ToDecimal(ctbCustomValue.Text), cmbCustomType.Text, strOrderNo);

                DataTable dt = clsData.getOrderDetailByOrderNo(strOrderNo);
                if (dt.Rows.Count == 0)
                {
                    MessageBox.Show("Order No. " + strOrderNo + " doesn't exist");
                    return;
                }
                DataTable dtSender = clsData.getSenderDataById(Convert.ToInt32(cmbSender.SelectedValue));
                if (dtSender.Rows.Count > 0 && dt.Rows.Count > 0)
                {
                    //We will name shipping label excel template file names as number for e.g. 1 , 2 , 3 , 4 , 4 etc. 
                    //user will put of these values and then system will know which shipping label excel template to use for all these orders.
                    string printLabelName = numUpDownPrintLabelRype.Value.ToString() + ".xlsx";

                    //string path = AppDomain.CurrentDomain.BaseDirectory + @printLabelName;                       
                    string path = fileLocation + @printLabelName;

                    bool isPathCorrect = true;
                    try
                    {
                        object missingValue = Type.Missing;

                        xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                        //MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());                                           
                        xlWorkSheet.Cells.Replace("SenderName", dtSender.Rows[0]["senderName"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderCompanyName", dtSender.Rows[0]["senderCompanyName"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderAdressLine1", dtSender.Rows[0]["senderAdressLine1"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderAddressLine2", dtSender.Rows[0]["senderAddressLine2"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderCity", dtSender.Rows[0]["senderCity"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderState", dtSender.Rows[0]["senderState"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderZIP", dtSender.Rows[0]["senderZIP"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("SenderCountry", dtSender.Rows[0]["senderCountry"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("OrderNumber", dt.Rows[0]["orderNo"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("Zone", dt.Rows[0]["zone"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                        xlWorkSheet.Cells.Replace("CustomerName", dt.Rows[0]["customerName"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                        xlWorkSheet.Cells.Replace("CustomerCompanyName", dt.Rows[0]["cCompanyName"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                        object misval = Type.Missing;



                      //Replace values by database values here


                        xlWorkSheet.Cells.Replace("CustomerAddressLine1", dt.Rows[0]["cAddressLine1"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressLine2", dt.Rows[0]["cAddressLine2"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressLine3", dt.Rows[0]["cAddressLine3"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressCity", dt.Rows[0]["cAddressCity"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressState", dt.Rows[0]["cAddressState"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressZIP", dt.Rows[0]["cAddressZIP"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomerAddressCountry", dt.Rows[0]["cAddressCountry"].ToString(), missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                        xlWorkSheet.Cells.Replace("ProductType", cmbProdType.Text, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomsType", cmbCustomType.Text, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                        xlWorkSheet.Cells.Replace("CustomsValue", ctbCustomValue.Text, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                        xlWorkBook.PrintOut(1, 1, 1, false, misValue, misValue, misValue, misValue);

                        xlWorkBook.Close(false, misValue, misValue);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Can't open Label Template, please check location.");
                        isPathCorrect = false;
                    }
                }
                else {
                    MessageBox.Show("This Sender doesn't exist.");
                }
            }
            xlApp.Quit();

can you show us some code ? which version of excel and vs are you using ? — Ashkan
– Ashkan, Commented Oct 3, 2012 at 6:44
Agree on showing some code but all versions still show almost same functions and properties (especially where he's stuck) — HichemSeeSharp
– HichemSeeSharp, Commented Oct 3, 2012 at 6:47

Matt Klein · Accepted Answer · 2012-10-03 06:58:11Z

1

so if value is not present then I needs to delete the cell only(as I can't delete row) and shift its just below cell to that position or merge it with below cell

You want to use the .Range.Delete(Shift) method that is given in the Excel PIA DLLs (the interop DLLs which I'm sure you're already using).

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.delete(v=office.11).aspx

xlDeleteShiftDirection

Once you find the cell that you want to delete, use something like this (which part of was lifted from your code example):

UNTESTED

xlWorkSheet.get_Range("A1", "A1").Delete(XlDeleteShiftDirection.xlShiftUp);

answered Oct 3, 2012 at 6:58

Matt Klein

8,4747 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ram Over a year ago

yes, I searched this method but I am not able to find the cell. I needs to find the cell based on the "some special strings". These strings values are fixed . Is there anyway, I can find cells based on the string value?

Ram Over a year ago

I had find the way to find cell based on string. It is simple Range.Find("string", some parameters). Thanks for your suggestions, it worked finally. Thanks a lot :)

Collectives™ on Stack Overflow

excel integration with c# .net

1 Answer 1

UNTESTED

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UNTESTED

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related