Click here to Skip to main content
14,735,400 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how to export data from excel sheet 1 to excel sheet2 using c#




in excel sheet 1 to excel sheet 2(in excel)
Posted
Updated 9-Nov-20 11:00am
Comments
Kuthuparakkal 20-Mar-13 7:20am
   
Same excel file at upstream and downstream end?

Please look at this LINK with required code.

Cheers,
Sudhakar
   
Try
http://support.microsoft.com/kb/306023[^]
Write Data to Excel using C#[^]

Note that transferring data between excels would be like exporting data from one excel and importing to another.
   
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace SplitExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
String fileName_in = openFileDialog1.FileName;
// --------------------------------------------------------------------------------- open input file
Excel.Application xlApp_in = new Microsoft.Office.Interop.Excel.Application();

if (xlApp_in == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook_in;
Excel.Worksheet xlWorkSheet_in;
Excel.Range range;
xlWorkBook_in = xlApp_in.Workbooks.Open(fileName_in, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet_in = (Excel.Worksheet)xlWorkBook_in.Worksheets.get_Item(1);
range = xlWorkSheet_in.UsedRange;

// --------------------------------------------------------------------------------- open output file
/*
Excel.Application xlApp_out = new Microsoft.Office.Interop.Excel.Application();

if (xlApp_out == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook_out;
Excel.Worksheet xlWorkSheet_out;
object misValue = System.Reflection.Missing.Value;
xlWorkBook_out = xlApp_out.Workbooks.Add(misValue);
xlWorkSheet_out = (Excel.Worksheet)xlWorkBook_out.Worksheets.get_Item(1);
*/
// --------------------------------------------------------------------------------- create new filename
// String fileName_in_ext = Path.GetExtension(fileName_in);
String fileName_in_ext = ".xls";
String fileName_in_nam = Path.GetFileNameWithoutExtension(fileName_in);
String fileName_in_pad = Path.GetDirectoryName(fileName_in);

int rCnt;
int cCnt;
int rw = 0;
int cl = 0;

rw = range.Rows.Count;
rw = 10;
cl = range.Columns.Count;

for (rCnt = 1; rCnt <= rw; rCnt++)
{
Excel.Application xlApp_out = new Microsoft.Office.Interop.Excel.Application();

if (xlApp_out == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook_out;
Excel.Worksheet xlWorkSheet_out;
object misValue = System.Reflection.Missing.Value;
xlWorkBook_out = xlApp_out.Workbooks.Add(misValue);
xlWorkSheet_out = (Excel.Worksheet)xlWorkBook_out.Worksheets.get_Item(1);

progressBar1.Value = (100 * rCnt) / rw;
for (cCnt = 1; cCnt <= cl; cCnt++)
{
try
{
xlWorkSheet_out.Cells[rCnt, cCnt].value = xlWorkSheet_in.Cells[rCnt, cCnt].value;
}
catch { };
}
String fileName_out = fileName_in_pad + "\\" + fileName_in_nam + rCnt.ToString() + fileName_in_ext;
xlWorkBook_out.SaveAs(fileName_out, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook_out.Close(true, misValue, misValue);
xlApp_out.Quit();
Marshal.ReleaseComObject(xlWorkSheet_out);
Marshal.ReleaseComObject(xlWorkBook_out);
Marshal.ReleaseComObject(xlApp_out);
}
xlWorkBook_in.Close(true, null, null);
Marshal.ReleaseComObject(xlWorkSheet_in);
Marshal.ReleaseComObject(xlWorkBook_in);
Marshal.ReleaseComObject(xlApp_in);

MessageBox.Show("Excel file created");
}
}
}
}
   
Comments
Richard Deeming 10-Nov-20 4:56am
   
An unformatted, unexplained code-dump is not a solution to this question.

Especially since the question was tagged "ASP", and Office Interop is not supported in any unattended application:

Considerations for server-side Automation of Office[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900