Excel Metadata
浏览数：123 /
时间：2015年06月09日
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
/*
当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
*/
/*
列类型 经典 ADO ADO.Net （OleDb）
数字 5-adDouble 5-OleDbType.Double
货币 6-adCurrency 6-OleDbType.Currency
日期/时间 7-adDate 7-OleDbType.Date
布尔值 11-adBoolean 11-OleDbType.Boolean
文本 < 255 202-adVarWChar 130-OleDbType.WChar
备注 203-adLongVarWChar 130-OleDbType.WChar
*/
namespace ExcelGenerator
{
/// <summary>
/// http://support.microsoft.com/zh-cn/kb/318452
/// http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled
/// 20150325
/// 涂聚文
/// </summary>
public partial class ExcelFileForm : Form
{
//"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0";
//Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties = "Excel 12.0 Macro;HDR=YES";
//Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";
//Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties = "Excel 8.0;HDR=YES";
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls; Extended Properties = "Excel 8.0;HDR=Yes;IMEX=1";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;";
private OleDbConnection cn;
private DataTable dtTables;
private CurrencyManager cm;
private DataTable dtColumns;
private DataView dvColumns;
string fileurl = string.Empty;
/// <summary>
/// GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]{null,null, strTable, null});
/// </summary>
/// <returns></returns>
private DataTable setTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("id",typeof(int));
dt.Columns.Add("name", typeof(string));
dt.Rows.Add(1, "tables");//工作表
dt.Rows.Add(2, "Columns");
//dt.Rows.Add(3, "");
//dt.Rows.Add(4, "");
//dt.Rows.Add(5, "");
//dt.Rows.Add(6, "");
return dt;
}
/// <summary>
/// /
/// </summary>
public ExcelFileForm()
{
InitializeComponent();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ExcelFileForm_Load(object sender, EventArgs e)
{
this.comboBox1.DataSource = setTable();
this.comboBox1.DisplayMember = "name";
this.comboBox1.ValueMember = "id";
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
//JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif
openFileDialog1.FileName = "";
openFileDialog1.Filter = "Excel 2000-2003 files(*.xls)|*.xls|Excel 2007 files (*.xlsx)|*.xlsx";//|(*.xlsx)|*.xlsx Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 1;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
if (!openFileDialog1.FileName.Equals(String.Empty))
{
connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;";
fileurl = openFileDialog1.FileName;
this.textBox1.Text = openFileDialog1.FileName;
}
}
}
/// <summary>
/// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_GUID,DESCRIPTION,TABLE_PROPID,DATE_CREATED,DATE_MODIFIED
/// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_GUID,COLUMN_PROPID,ORDINAL_POSITION,COLUMN_HASDEFAULT,COLUMN_DEFAULT,COLUMN_FLAGS,IS_NULLABLE,DATA_TYPE,TYPE_GUID,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME,DESCRIPTION
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
ExcelConnection.Open();
//dtTables = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//dtTables = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, "Sheet1$" });
//dtTables = ExcelConnection.GetSchema("tables");
//this.dataGridView1.DataSource = dtTables;
//ExcelConnection.Close();
//this.textBox2.Text = GetColumnNames(dtTables);
GetTablesList();
this.dataGridView2.DataSource= ReadExcelFile().Tables[0];
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static string GetColumnNames(System.Data.DataTable table)
{
if (table != null)
{
List<string> lstColumn = new List<string>();
foreach (System.Data.DataColumn col in table.Columns)
{
lstColumn.Add(col.ColumnName);
}
return String.Join(",", lstColumn.ToArray());
}
return string.Empty;
//foreach (DataRow row in table.Rows)
//{
// foreach (DataColumn column in table.Columns)
// {
// ColumnName = column.ColumnName;
// ColumnData = row[column].ToString();
// }
//}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
}
/// <summary>
///
/// </summary>
private void GetTablesList()
{
try
{
cn = new OleDbConnection(connectionString);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
this.dataGridView3.DataSource = dtTables;
//dataGridView1.ReadOnly = true;
cn.Close();
}
catch (System.Data.OleDb.OleDbException myException)
{
for (int i = 0; i < myException.Errors.Count; i++)
{
MessageBox.Show("Index #" + i + "\n" +
"Message: " + myException.Errors[i].Message + "\n" +
"Native: " +
myException.Errors[i].NativeError.ToString() + "\n" +
"Source: " + myException.Errors[i].Source + "\n" +
"SQL: " + myException.Errors[i].SQLState + "\n");
}
}
GetColumnsList();
}
/// <summary>
///
/// </summary>
private void GetColumnsList()
{
try
{
if (cm == null)
cm = (CurrencyManager)this.BindingContext[dtTables];
cm.PositionChanged += new EventHandler(cm_PositionChanged);
int r = cm.Position;
String strTable = dtTables.Rows[r]["TABLE_NAME"].ToString();
cn = new OleDbConnection(connectionString);
cn.Open();
dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, strTable, null });//字段名
dvColumns = new DataView(dtColumns);
dvColumns.Sort = "ORDINAL_POSITION";
dataGridView1.DataSource = dvColumns;
//dataGridView1.ReadOnly = true;
cn.Close();
this.textBox2.Text = GetColumnNames(dtColumns);
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cm_PositionChanged(object sender, System.EventArgs e)
{
GetColumnsList();
}
private void comboBox1_SelectedValueChanged(object sender, EventArgs e)
{
}
/// <summary>
///
/// </summary>
/// <param name="dt2format"></param>
private void FormatTablesGrid(DataTable dt2format)
{
DataGridTableStyle gs = new DataGridTableStyle();
gs.MappingName = dt2format.TableName;
DataGridColumnStyle cs = new DataGridTextBoxColumn();
cs.MappingName = "TABLE_NAME";
cs.HeaderText = "Table Name";
cs.Width = 75;
gs.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.MappingName = "TABLE_TYPE";
cs.HeaderText = "Table Type";
cs.Width = 75;
gs.GridColumnStyles.Add(cs);
CurrencyManager cm = (CurrencyManager)this.BindingContext[dt2format];
PropertyDescriptor pd = cm.GetItemProperties()["DATE_CREATED"];
cs = new DataGridTextBoxColumn(pd, "d");
cs.MappingName = "DATE_CREATED";
cs.HeaderText = "Date Created";
cs.Width = 75;
gs.GridColumnStyles.Add(cs);
cm = (CurrencyManager)this.BindingContext[dt2format];
pd = cm.GetItemProperties()["DATE_MODIFIED"];
cs = new DataGridTextBoxColumn(pd, "d");
cs.MappingName = "DATE_MODIFIED";
cs.HeaderText = "Date Modified";
cs.Width = 75;
gs.GridColumnStyles.Add(cs);
//dataGridView1.TabIndex.Add(gs);
button2.Enabled = false;
}
/// <summary>
/// http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB
/// </summary>
/// <returns></returns>
private string GetConnectionString()
{
Dictionary<string, string> props = new Dictionary<string, string>();
// XLSX - Excel 2007, 2010, 2012, 2013
//props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
//props["Extended Properties"] = "=Excel 12.0 XML;"; //
//props["Data Source"] = fileurl;
// XLS - Excel 2003 and Older
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Extended Properties"] = "Excel 8.0;";//HDR=Yes;IMEX=1
props["Data Source"] = fileurl;
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append(‘=‘);
sb.Append(prop.Value);
sb.Append(‘;‘);
}
return sb.ToString();
}
/// <summary>
/// 创建工作表table1
/// </summary>
private void WriteExcelFile()
{
string connectionString = GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";//创建工作表table1 及表头
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,‘AAAA‘,‘2014-01-01‘);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, ‘BBBB‘,‘2014-01-03‘);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, ‘CCCC‘,‘2014-01-03‘);";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE [table1] SET name = ‘DDDD‘ WHERE id = 3;";
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// 读工作表
/// </summary>
/// <returns></returns>
private DataSet ReadExcelFile()
{
DataSet ds = new DataSet();
string connectionString = GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
/// <summary>
/// 添加一个新工作表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
WriteExcelFile();
}
/// <summary>
/// 查询值
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
//create a string for the query
string ExcelQuery;
//Sheet1 is the sheet name
//create the query:
//read column with heading A from the Excel file
ExcelQuery = "SELECT * FROM [Sheet1$] WHERE 序号=5"; // from Sheet1";
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
//OleDbCommand cmd = new OleDbCommand();
//cmd.Connection = conn;
//cmd.CommandText = ExcelQuery;//创建工作表table1 及表头
//cmd.ExecuteNonQuery();
OleDbDataAdapter command = new OleDbDataAdapter(ExcelQuery, connectionString);
command.Fill(ds, "ds");
}
dataGridView1.DataSource = ds.Tables[0];
}
}
}
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
