c#インポートエクスポートexcel
1)テストで意味のないexcelファイルもdataTableに変換できる
2)IISでは前の記事に従って配置する必要がある
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();
}
}
}
}
}