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");
}
}
}
}