一、DataTable导入导出Excel
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApplication1.UserClass
{
class FileHelper
{
/// <summary>
/// Excel导出为DataSet
/// </summary>
public static DataSet ExcelToDs(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;HDR=False;IMEX=1";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}
/// <summary>
/// Excel导出为DataTable
/// </summary>
public DataTable ExcelToDataTable(string Path)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(Path);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
DataSet ds = new DataSet();
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds.Tables[0];
}
/// <summary>
/// 将DataTable数据保存为Excel文件
/// </summary>
public bool SaveToExcel(DataTable dt)
{
string Path = SaveDialog("(*.xls)|*.xls");
bool success = true;
string connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Path + ";Extended Properties=Excel 8.0;";//仅支持2003格式的Excel
OleDbConnection conn = new OleDbConnection(connStr);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
string cmdText = "Create Table sheet1 (";
for (int i = 0; i < dt.Columns.Count; i++)
{
string columnName = dt.Columns[i].ColumnName;
cmdText += " " + columnName;
string typeName = GetExcelMapDataType(dt.Columns[i].DataType.Name);
cmdText += " " + typeName;
if (i == dt.Columns.Count - 1)
{
cmdText += ")";
}
else
{
cmdText += ",";
}
}
cmd.CommandText = cmdText;
cmd.ExecuteNonQuery();
for (int i = 0; i < dt.Rows.Count; i++)
{
cmdText = "Insert into sheet1 values (";
for (int j = 0; j < dt.Columns.Count; j++)
{
string columnName = dt.Columns[j].ColumnName;
cmdText += "@" + columnName;
cmd.Parameters.AddWithValue("@" + columnName, dt.Rows[i][columnName]);
if (j == dt.Columns.Count - 1)
{
cmdText += ")";
}
else
{
cmdText += ",";
}
}
cmd = new OleDbCommand(cmdText, conn);
for (int j = 0; j < dt.Columns.Count; j++)
{
string columnName = dt.Columns[j].ColumnName;
string columnType = dt.Columns[j].DataType.Name;
if (columnType.ToLower() == "datetime")
{
OleDbParameter pm = new OleDbParameter();
pm.ParameterName = "@" + columnName;
pm.OleDbType = OleDbType.Date;
pm.Value = dt.Rows[i][columnName];
cmd.Parameters.Add(pm);
continue;
}
cmdText += "@" + columnName;
cmd.Parameters.AddWithValue("@" + columnName, dt.Rows[i][columnName]);
}
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
conn.Dispose();
}
return success;
}
/// <summary>
/// 保留单元格格式导出
/// </summary>
static string GetExcelMapDataType(string dataType)
{
string result = dataType;
switch (dataType.ToLower())
{
case "int":
case "int32":
result = "INTEGER";
break;
case "datetime":
result = "DATE";
break;
case "boolean":
result = "bit";
break;
default:
break;
}
return result;
}
/// <summary>
/// 将DataSet数据保存为Excel文件
/// </summary>
public bool DataSetToExcel(DataSet ds)
{
string Path = SaveDialog("(*.xls)|*.xls");
string strCon = string.Empty;
FileInfo file = new FileInfo(Path);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
break;
case ".xlsx":
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'";
break;
default:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
break;
}
try
{
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
System.Data.OleDb.OleDbCommand cmd;
try
{
for (int i = 0; i < ds.Tables.Count; i++)
{
cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", ds.Tables[i].TableName), con); //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
cmd.ExecuteNonQuery();
}
}
catch
{
return false;
}
//创建表格字段
for (int i = 0; i < ds.Tables.Count; i++)
{
strSQL.Clear();
strSQL.Append("CREATE TABLE ").Append("[" + ds.Tables[i].TableName + "]");
strSQL.Append("(");
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
strSQL.Append("[" + ds.Tables[i].Columns[j].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
//添加数据
for (int k = 0; k < ds.Tables[i].Rows.Count; k++)
{
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
strvalue.Append("'" + ds.Tables[i].Rows[k][j].ToString() + "'");
if (j != ds.Tables[i].Columns.Count - 1)
{
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = strSQL.Append(" insert into [" + ds.Tables[i].TableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
}
con.Close();
return true;
}
}
catch
{
return false;
}
}
}
}
二、读取Excel单元格字符串截断问题
1、问题描述：
表格某一单元格内容过长，则上位机读取Excel表时，字符串将被截断，导致数据缺失，对后续数据处理造成错误。
2、问题分析：
目前微软提供了两种Office
Ole驱动来实现数据库连接，分别为ACE版本和Jet版本。由于Jet引擎只可以访问 Office
97-2003，故上位机代码中采用了调用Microsoft.ACE.OLEDB.12.0驱动的方式，利用Microsoft
ACE引擎连接到工作簿,将Excel文件作为数据源来读写。
而OleDb读取文件的方式受Excel ISAM（Indexed
Sequential Access Method，即索引顺序存取方法）驱动程序的限制。在读取过程中，Excel ISAM
驱动程序通过扫描sheet中前几行（默认为8行）的内容来确定一个
Excel列的最合适的数据类型，然后选择能够代表其样本中大部分值的数据类型，分配存储大小。该数据类型通常为varchar，其大小为255字符。而实际使用中，常常会出现该列前8行字符串在255个字符以内，8行以外的字符串却大于255个字符的情况，那么这个时候使用OleDb读取Excel就会将后面大于255的字符串截断。
3、改善措施与验证结果：
经翻查资料，得到的解决方案为，通过修改注册表值来更改采样行数。修改路径如下（Excel 2016）：
计算机\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engines\Excel
TypeGuessRows取值范围为0-16。当TypeGuessRows = 0时会扫描整个页面再匹配合适的数据类型。但试验后发现，该方法只适用于Jet引擎，对于使用ACE引擎的上位机，该方法无效。
在不修改表格读取方式的情况下，一个可以实现字符串正常读取的措施是，把数据大于255的表格放到前8行。在前8行的判定中，数据已经超过255，则该列的数据类型会被设定得更大，就不会出现截断的情况。
由于该方法会增加额外的工作量，不宜作为永久措施。目前的最佳方案是修改文件读取方式，用NPOI方法读取Excel表格。该方法不受ISAM驱动程序限制，可以保证字符串的完整性，同时可以满足读取高版本Excel文件的需求。
经调试，可以确保该方法能够完整读取单元格长度远大于255的字符串。
4、总结：
在处理单一、长度较短的表格时，可以优先选择OleDb方式对表格进行读取；若每一单元格包含的数据类型较为复杂，则推荐在编写上位机时使用NPOI方法读取表格，保证数据处理的可靠性。
标签：OleDb,C#,cmd,Excel,new,Data,ds,string From： https://www.cnblogs.com/Mars-0603/p/16731148.html