c#怎样导入excel里面的二维数据,并导出的二维数组也放在excel里?
解决方案 »
- VS2010 C#查msdn的问题.
- 如何扩展window.window.external中的方法
- c# 操作MYSQL 读取数据出现问题。
- WinForm 读取word内容 并判断表格问题 急 在线等!!! 分不够 可以再加
- dropdownlist
- ~~~画饼图遇到的问题,请高手指点一下!
- 有没有用 C# 开发服务程序,并且在服务程序中成功加载虚拟设备驱动的?
- 有没有人用Remoting时见过这个问题?DataSet中所有数据都为DBNull?
- 1.0升级1.1代来的问题Request[]出错
- 用sqlconnection连接带密码的ACCESS数据库,连接字串该怎么写?
- 新手求教,关于读取Excel的问题!
- 中恒基超级旋风数字监控系统 如何用在程序中?
Excel.Range range = (Excel.Range)workSheet.Cells[top,left];
range = range.get_Resize(row,colCount);
range.Value = arr;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds= new DataSet();
OleDaExcel.Fill(ds);
OleConn.Close();
}
public System.Data.DataTable ImportFile(string filePath)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim(); string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "Select * From [" + tableName + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, tableName);
return ds.Table[0];
}
此函数得到的结果为一个DataTable,然后转为一个二维数据就可以了,其实DataTable形式上也是一个二维数组
2.导出二维数组到EXcel中
二维数组名称为 string[a][b] array;
其中函数参数myStream从你保存文件时SaveDialogue对话框中获得
Stream myStream;
myStream = saveFileDialog.OpenFile();函数如下:
public Boolean ExportFile(string[a][b] array, Stream myStream)
{
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string str = ""; try
{
//写内容 for (int j = 0; j < b; j++)
{ string tempStr = ""; for (int k = 0; k < a; k++)
{ if (k > 0)
{ tempStr += "\t"; } tempStr += array[j][k].ToString(); }
sw.WriteLine(tempStr); } sw.Close(); myStream.Close();
return true; } catch (Exception e1)
{ // MessageBox.Show(e1.ToString());
return false; } finally
{ sw.Close(); myStream.Close();
//return false; }
}
导出:
/// <summary>
/// Protected internal関数:DataSet->EXCELに出力(二つパラメータがある関数)
/// </summary>
/// 作成日:2010/03/30
/// 修正日:
/// 作成者:張玉剛
/// 修正者:
/// <param name="dataSet">DataSet</param>
/// <param name="outputPath">ファイルのパス</param>
/// <res>[TRECHINAComment]</res>
protected internal void ExportToExcel(DataSet dataSet, string outputPath)
{
try
{
//新しいファイルを生成
outputPath = GetNewFilePath(outputPath);
//ファイルのパースの値があるかどうかことを判断
if(outputPath == "")
{
return;
}
excelApp = new Excel.ApplicationClass();
//EXCELのバージョンを判断する
if (excelApp == null)
{
throw new Exception(str_Exception2);
} excelWorkbook = excelApp.Workbooks.Add(Type.Missing); int sheetIndex = 0; //DataTableのデータをEXCELに書く
foreach (System.Data.DataTable dt in dataSet.Tables)
{
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
//列名前を取る
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
} //データを対象に書く
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
} //変数初期化
string finalColLetter = string.Empty; string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length; //列の長さが長すぎ、以下の算法を転化
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
} finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1); //セールの数を追加する
excelSheet = (Excel.Worksheet) excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
//EXCELのセットの名前を設定
excelSheet.Name = dt.TableName; string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
//データをEXCELに追加する
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
Excel.Range r = excelSheet.get_Range(excelRange, Type.Missing); //EXCELのフォーマットを設定する
ExcelFormat(dataSet); }
//保存
excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//資源を釈放
ProcessDispose();
}
catch (UnauthorizedAccessException)
{
throw new Exception(str_Exception1);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
} }
/// <summary>
/// Protected internal関数:EXCELファイルから、データを取込(一つパラメータがある関数)
/// </summary>
/// 作成日:2010/04/13
/// 修正日:
/// 作成者:張玉剛
/// 修正者:
/// <param name="outputPath">ファイルのパス</param>
/// <res>[TRECHINAComment]</res>
protected internal DataSet ImportExcelData(string outputPath)
{
try
{
//ファイルのパースの値があるかどうかことを判断
if(outputPath == "")
{
return null;
} excelApp = new Excel.ApplicationClass(); //EXCELのバージョンを判断する
if (excelApp == null)
{
throw new Exception(str_Exception2);
} //EXCELファイルを開ける
excelWorkbook = excelApp.Workbooks.Open(outputPath,0,false,5,"","",false,Excel.XlPlatform.xlWindows,"",
true,false,0,true,1,0);
//EXCELのセットの数量を取る
int n = excelWorkbook.Worksheets.Count; string[] SheetSet = new string[n]; //EXCELの名前を取る
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Excel.Worksheet)excelWorkbook.Worksheets[i]).Name;
} //資源の釈放
ProcessDispose(); //対象初期化
ds = new DataSet();
//データ接続文字列
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + outputPath + ";" +
"Extended Properties='Excel 8.0;HDR=No;IMEX=1'"; using (OleDbConnection conn = new OleDbConnection(strConn))
{
//データベースを接続
conn.Open(); //EXCELファイルの内容をDataSetに添加する
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$]"; OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); //DataSetに添加する
da.Fill(ds, SheetSet[i - 1]); //資源の釈放
da.Dispose();
} } return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
{
using (FileStream fileStream = new FileStream(filePathName, FileMode.CreateNew, FileAccess.ReadWrite))
{
StringBuilder stringBuilder = new StringBuilder();
using (StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.Default))
{
int colCount = GVPassDetail.Columns.Count; for (int i = 0; i < colCount; i++)
{
stringBuilder.Append(GVPassDetail.Columns[i].HeaderText);
if (i < colCount - 1)
{
stringBuilder.Append(",");
}
}
stringBuilder.Append("\n"); //换行 for (int i = 0; i < GVPassDetail.Rows.Count; i++)
{
for (int j = 0; j < GVPassDetail.Columns.Count; j++)
{
stringBuilder.Append(GVPassDetail.Rows[i].Cells[j].Text.Trim());
if (j < GVPassDetail.Columns.Count - 1)
{
stringBuilder.Append(",");
}
}
stringBuilder.Append("\n");
}
streamWriter.Write(stringBuilder.ToString());
streamWriter.Close(); }
}
}
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.SqlClient;
//using Microsoft.Office.Interop.Excel;
using Microsoft.CSharp;namespace WindowsSecretPipe
{
public partial class DataDaoChu : Form
{
public DataDaoChu()
{
InitializeComponent();
}
private static string ConnectString = "server=(local);database=IT Industry;uid=sa;pwd=";
DataTable dt = new DataTable();
private void DataDaoChu_Load(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(ConnectString);
SqlDataAdapter da = new SqlDataAdapter("select * from PipeType", connection);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
//----------------------------
public void printAll(System.Data.DataTable dt)
{
//导出到execl
try
{
//没有数据的话就不往下执行
if (dt.Rows.Count == 0)
return;
//实例化一个Excel.Application对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错
excel.Application.Workbooks.Add(true); //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写
excel.Visible = false;
//生成Excel中列头名称
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
} //把DataGridView当前页的数据保存在Excel中
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行,上下的距离,就是可以到Excel最下的行数,比数据长了报错,比数据短了会显示不完
{
for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列,左右的距离,就是可以到Excel最右的列数,比数据长了报错,比数据短了会显示不完
{
string str = dt.Rows[i][j].ToString();
excel.Cells[i + 2, j + 1] = "'" + str;//i控制行,从Excel中第2行开始输出第一行数据,j控制列,从Excel中第1列输出第1列数据,"'" +是以string形式保存,所以遇到数字不会转成16进制
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false; //保存工作簿,值为false会报错
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
excel.Save("D:" + "\\KKHMD.xls");
//确保Excel进程关闭
excel.Quit();
excel = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
public void button2_Click(object sender, EventArgs e)
{
printAll(dt);
MessageBox.Show("数据导出成功!");
}
}
}
#region 读取Excel文档
/// <summary>
/// 读取Excel文档
/// </summary>
public System.Data.DataTable ExcelToDT()
{
OpenFileDialog openFillDialog = new OpenFileDialog();
openFillDialog.Filter = "Microsoft Excel file (*.xls)|*.xls";
string fileName;
string connectionString;
System.Data.DataTable table = new System.Data.DataTable();
if (openFillDialog.ShowDialog() == DialogResult.OK)
{
fileName = openFillDialog.FileName;
connectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";extended properties = 'Excel 8.0;HDR = NO;I MEX =1;'";
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string strSQL = "select * from [MySheet$]";
OleDbCommand cmd = new OleDbCommand(strSQL, con);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(table);
con.Close();
adapter.Dispose();
} return table;
}
#endregion
http://blog.csdn.net/gwf25sz/archive/2009/10/10/4649032.aspx
using System.Data.OleDb
/// <summary>
/// 上传文件并返回数据(读Excal)
/// </summary>
/// <param name="ifile"></param>
/// <param name="filePath">存放文件的路径</param>
/// <param name="fileName">要保存的文件名</param>
/// <param name="nameList">返回Table的列名的集合,一般就是数据库的字段名(比如 [l2_code l2_desc])</param>
/// <returns>返回上传的数据集</returns>
public DataTable importExcalGetData(System.Web.UI.HtmlControls.HtmlInputFile ifile, string filePath, string fileName, List<string> nameList)
{
string iname = ifile.PostedFile.FileName;
iname = fileName + Path.GetExtension(iname);
if (File.Exists(filePath + iname))
{
File.Delete(filePath + iname);
}
ifile.PostedFile.SaveAs(filePath + iname); string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + iname + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", conn);
DataSet ds = new DataSet();
adp.Fill(ds);
try
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
ds.Tables[0].Columns[i].ColumnName = nameList[i]; }
return ds.Tables[0];
}
catch (Exception)
{ return null;
}
}
/// Table装换成Excal(写Excal)
/// </summary>
/// <param name="path">table生成的Excal存放的路径</param>
/// <param name="dt">要装换的Table数据源</param>
/// <param name="herdName">数据源的字段名(如:[颜色代码 颜色名称],另:“注意顺序!!”)</param>
public void TableToExcal(string filePath, DataTable dt, List<string> herdName)
{
FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("GB2312"));
List<string> dtNameList = new List<string>();
// 数据源字段名
string name = "";
for (int a = 0; a < herdName.Count; a++)
{
name += (herdName[a] + "\t");
}
//去掉字符串最后的'\t'
name = name.Substring(0, (name.LastIndexOf("\t") - 1)); sw.WriteLine(name);
for (int j = 0; j < dt.Columns.Count; j++)
{
dtNameList.Add(dt.Columns[j].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
string swWrit = "";
for (int z = 0; z < dtNameList.Count; z++)
{
swWrit += (dt.Rows[i][dtNameList[z].ToString()] + "\t");
}
sw.WriteLine(swWrit);
}
sw.Close(); }
C#写的——
public static int GetBookNumber(string input)
{
int Number = 0;
string FilterStr = @"\Service";
string activePath = @"DBData\TestData.xls";
System.Web.UI.Page tempPath = new System.Web.UI.Page();
string fileFullDir = tempPath.Server.MapPath(activePath);
if (fileFullDir.IndexOf(FilterStr) > -1)
{
fileFullDir = fileFullDir.Remove(fileFullDir.IndexOf(FilterStr), 8);
}
List<string[]> TEST = new List<string[]>();
try
{
//string _kind = \'
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileFullDir + " ;Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] where leftkind = \'" + input + "\'";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "bookinfo");
DataTable dt = ds.Tables[0];
Number = dt.Rows.Count;
}
catch (Exception e)
{
string err = string.Format("The process failed: {0}", e.ToString());
//Response.Write(err); }
return Number;
}
问题解决了吗,我忘了把引用写出来了
using System.Data.OleDb;
这是引用可以解决strConn的报错问题
第三方,不需要excel类库