c#インポートエクスポートexcel

5088 ワード

1)テストで意味のないexcelファイルもdataTableに変換できる
2)IISでは前の記事に従って配置する必要がある
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
namespace ExcelTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
        }
        System.Data.DataTable dt = new System.Data.DataTable();
        /// <summary>
        ///  
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInput_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Files|*.xls";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string filename = ofd.FileName;
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook;
                Microsoft.Office.Interop.Excel.Worksheet worksheet;
                object oMissing = System.Reflection.Missing.Value;
                workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                worksheet = (Worksheet)workbook.Worksheets[1];
                int rowCount = worksheet.UsedRange.Rows.Count;
                int colCount = worksheet.UsedRange.Columns.Count;
                Microsoft.Office.Interop.Excel.Range range1;

                int i;
                for (i = 0; i < colCount; i++)
                {
                    range1 = worksheet.get_Range(worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]);
                    dt.Columns.Add(range1.Value2.ToString());
                }
                int j;
                for (j = 1; j < rowCount; j++)
                {
                    DataRow dr = dt.NewRow();
                    for (i = 0; i < colCount; i++)
                    {
                        range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]);
                        dr[i] = range1.Value2;
                    }
                    dt.Rows.Add(dr);
                }
                excel.Quit();
            }
            else
            {
                MessageBox.Show(" !");
            }
        }
        /// <summary>
        ///  
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOutput_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();   // SaveFileDialog 
            dlg.Filter = "Excel files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = "";
                try
                {
                    // 
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (i > 0)
                        {
                            columnTitle += "\t";
                        }
                        columnTitle += dt.Columns[i].ColumnName;
                    }
                    sw.WriteLine(columnTitle);
                    // 
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int k = 0; k < dt.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                columnValue += "\t";
                            }
                            if (dt.Rows[j][k] == null)
                                columnValue += "";
                            else
                                columnValue += dt.Rows[j][k].ToString().Trim();
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch
                {
                    MessageBox.Show(" ");
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }
    }
}