Need Advices. Im trying to do that but nothing comes out

Question

4.00/5 (1 vote)

See more:

Hello

so there is excel form for my job which must be filled when something sells.
In it are 19 rows for products.
I coded am app which is filling this form. Everything going good but my problem is if all rows where filled on first sale and second sales products quantity is example 5 it fills first 5 and another 14 rows is not be empty it is be full with first sales products. Below I will post my code

its already 3 day im trying to solve this problem.
I have code which must clear rows but its working only two rows

this is code : rangeProductNames.EntireRow.ClearContents();

Is There anything which can convert xls file with c# code (which will be create new excel file like this form)?
Can I do so that when code fills file it does not save but only prints it?
I need advices or good tutorials how to create new file

Sorry for my English.

Please Help me. It's very very important for me.

private void btnExportInExcel_Click(object sender, EventArgs e)
    {
      nudZednadebNumber.Value++;
      string address = txtFileAddress.Text;
      OpenExcelWorkbook(address);
      _sheet = (Excel.Worksheet)_sheets[1];
      _sheet.Select(Type.Missing);

     
      Excel.Range rangeProductNames = (Excel.Range)_sheet.get_Range(_sheet.Cells[27, "B"] as Excel.Range, _sheet.Cells[27 + dgvProducts.Rows.Count - 1, "B"] as Excel.Range);
      Excel.Range rangeProductQuantity = (Excel.Range)_sheet.get_Range(_sheet.Cells[27, "M"] as Excel.Range, _sheet.Cells[27 + dgvProducts.Rows.Count - 1, "M"] as Excel.Range);
      Excel.Range rangeProductPrice = (Excel.Range)_sheet.get_Range(_sheet.Cells[27, "O"] as Excel.Range, _sheet.Cells[27 + dgvProducts.Rows.Count - 1, "O"] as Excel.Range);
      Excel.Range rangeOneType = (Excel.Range)_sheet.get_Range(_sheet.Cells[27, "K"] as Excel.Range, _sheet.Cells[27 + dgvProducts.Rows.Count - 1, "K"] as Excel.Range);
      string[,] proNames = new string[dgvProducts.Rows.Count, 1];
      string[,] proQuantity = new string[dgvProducts.Rows.Count, 1];
      string[,] proPrice = new string[dgvProducts.Rows.Count, 1];
      string[,] proOneType = new string[dgvProducts.Rows.Count, 1];

      
         rangeProductNames.EntireRow.ClearContents();

 for (int i = 0; i < dgvProducts.Rows.Count; i++)
         {
           proNames[i, 0] = dgvProducts.Rows[i].Cells["cProductName"].Value.ToString();
           proOneType[i, 0] = dgvProducts.Rows[i].Cells["cOneType"].Value.ToString();
           proQuantity[i, 0] = dgvProducts.Rows[i].Cells["cQuantity"].Value.ToString();
           proPrice[i, 0] = dgvProducts.Rows[i].Cells["cCommonPrice"].Value.ToString();

         }

         rangeProductNames.set_Value(Type.Missing, proNames);
         rangeOneType.set_Value(Type.Missing, proOneType);
         rangeProductQuantity.set_Value(Type.Missing, proQuantity);
         rangeProductPrice.set_Value(Type.Missing, proPrice);

Posted 14-Nov-10 10:35am

lester555

Updated 14-Nov-10 18:37pm

JF2015

v2

Add a Solution

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)

Versile · Answer 1 · 2010-11-22T11:22:00

This is a great add on that I think you may already be using:
http://exceldatareader.codeplex.com/

Using that it would probably be easier to create a dataset from the file, manipulate it as you would like then write the dataset back to a "new" excel file, with a randomly generated filename (GUID works for me) and then run it... Example to get the dataset:

C#

DataSet output = new DataSet();
using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
    Excel.IExcelDataReader excelReader = fileName.IndexOf(".xlsx") > -1 ? Excel.ExcelReaderFactory.CreateOpenXmlReader(stream) :
                                                                          Excel.ExcelReaderFactory.CreateBinaryReader(stream);
    excelReader.IsFirstRowAsColumnNames = true;
    output = excelReader.AsDataSet(false);
}