110,533
社区成员
发帖
与我相关
我的任务
分享
void ReadCsvs()
{
if (File.Exists(this.tb_list.Text.Trim()))
{
// open the file "data.csv" which is a CSV file with headers
using (CachedCsvReader csv = new
CachedCsvReader(new StreamReader(tb_list.Text), true))
{
// Field headers will automatically be used as column names
dgv_show.DataSource = csv;
}
}
else
{
MessageBox.Show("文件名不能为空或文件不存在");
return;
}
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="obj">DataGridView数据集</param>
/// <param name="sTitle">文件名</param>
/// <param name="bChart">是否包括图表</param>
/// <returns>导出成功与否</returns>
public bool Save(DataGridView obj, string sTitle, bool bChart)
{
if (object.Equals(obj, null))
return false;
if (obj.Columns.Count == 0 || obj.Rows.Count == 0)
{
MessageBox.Show("没有需要保存的数据", "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.Title = "保存数据";
dlg.Filter = "Excel文件(*.xls)|*.xls|CSV文件(*.csv)|*.csv|TxT文件(*.txt)|*.txt";
dlg.FilterIndex = 1;
dlg.OverwritePrompt = true;
dlg.DefaultExt = "xls";
dlg.FileName = sTitle;
dlg.InitialDirectory = string.IsNullOrEmpty(sLastpath) ? Application.StartupPath : sLastpath;
if (dlg.ShowDialog() == DialogResult.OK)
{
Step(0);
sLastpath = dlg.FileName.Substring(0, dlg.FileName.LastIndexOf('\\'));
if (dlg.FilterIndex == 1)
{
return SaveInExcel(obj, sTitle, dlg.FileName, bChart);
}
else
{
using (Stream fs = dlg.OpenFile())
{
using (StreamWriter fw = new StreamWriter(fs))
{
StringBuilder st = new StringBuilder();
List<int> indexList = new List<int>(obj.Columns.Count);
for (int i = 0; i < obj.Columns.Count; i++)
{
indexList.Insert(obj.Columns[i].DisplayIndex, i);
}
Step(5);
foreach (var i in indexList)
{
st.Append(obj.Columns[i].HeaderText + "\t");
}
fw.WriteLine(st);
Step(10);
StringBuilder sc = null;
for (int i = 0; i < obj.Rows.Count; i++)
{
sc = new StringBuilder();
foreach (var j in indexList)
{
sc.Append(obj.Rows[i].Cells[j].Value.ToString() + "\t");
}
Step(90 / obj.Rows.Count * i);
fw.WriteLine(sc);
sc = null;
}
}
}
Step(100);
return true;
}
}
return false;
}
public static bool IsTextFile(string fileName){
string extName=Path.GetExtension(fileName).ToLower();
if(extName==".csv"
||extName=="txt"){
return true;
}
return false;
}
public static string DataTableToCSV(DataTable dt,string title,ListDictionary ld,bool isShowDBColName,Page page){
string path=page.MapPath(HttpRuntime.AppDomainAppVirtualPath)+"/Temp/";
if(!Directory.Exists(path)){
Directory.CreateDirectory(path);
}
string fileName=Guid.NewGuid()+".csv";
FileStream oStream=File.Create(path+fileName);
var oWriter=new StreamWriter(
oStream,
Encoding.GetEncoding("GB2312"));
//保证不能超出Excel所能承受的最大行数
if(dt!=null
&&dt.Rows.Count>=65535){
oWriter.Write("请增加查询条件,减少导出数据量。Excel最大支持65535行数据!"+",");
oWriter.Write(oWriter.NewLine);
oWriter.Flush();
oWriter.Close();
oStream.Close();
return path+fileName;
}
//添加标题
oWriter.Write(title+",");
oWriter.Write(oWriter.NewLine);
//显示表的标题
if(ld!=null){
foreach(string name in ld.Values){
oWriter.Write(name.Trim()+",");
}
oWriter.Write(oWriter.NewLine);
}
if(dt!=null){
//添加Column名称列
if(isShowDBColName){
foreach(string col in ld.Keys){
oWriter.Write(col+",");
}
oWriter.Write(oWriter.NewLine);
}
//添加具体数据
foreach(DataRow row in dt.Rows){
foreach(string col in ld.Keys){
string str=row[col].ToString().Trim().Replace(
",",
"/");
oWriter.Write(str+",");
}
oWriter.Write(oWriter.NewLine);
}
}
oWriter.Flush();
oWriter.Close();
oStream.Close();
return path+fileName;
}
public static string DataViewToCSV(DataView dv,string title,ListDictionary ld,bool isShowDBColName,Page page){
string path=page.MapPath(HttpRuntime.AppDomainAppVirtualPath)+"/Temp/";
if(!Directory.Exists(path)){
Directory.CreateDirectory(path);
}
string fileName=Guid.NewGuid()+".csv";
FileStream oStream=File.Create(path+fileName);
var oWriter=new StreamWriter(
oStream,
Encoding.GetEncoding("GB2312"));
//添加标题
oWriter.Write(title+",");
oWriter.Write(oWriter.NewLine);
//显示表的标题
if(ld!=null){
foreach(string name in ld.Values){
oWriter.Write(name.Trim()+",");
}
oWriter.Write(oWriter.NewLine);
}
if(dv!=null){
//添加Column名称列
if(isShowDBColName){
foreach(string col in ld.Keys){
oWriter.Write(col+",");
}
oWriter.Write(oWriter.NewLine);
}
//添加具体数据
for(int i=0;
i<dv.Count;
i++){
foreach(string col in ld.Keys){
string str=dv[i][col].ToString().Trim();
oWriter.Write(str+",");
}
oWriter.Write(oWriter.NewLine);
}
}
oWriter.Flush();
oWriter.Close();
oStream.Close();
return path+fileName;
}
#region 处理多个DataTable写入一个文件的函数组合
public static void BeginWrite(Page page){
string path=page.MapPath(HttpRuntime.AppDomainAppVirtualPath)+"/Temp/";
if(!Directory.Exists(path)){
Directory.CreateDirectory(path);
}
string fileName=Guid.NewGuid()+".csv";
strCurFileName=path+fileName;
oStream=File.Create(path+fileName);
oWriter=new StreamWriter(
oStream,
Encoding.GetEncoding("GB2312"));
}
public static void WriteTitle(string title){
//添加标题
oWriter.Write(title+",");
oWriter.Write(oWriter.NewLine);
}
public static void WriteContent(DataTable dt,ListDictionary ld,bool isShowDBColName){
if(dt!=null){
//添加Column名称列
if(isShowDBColName){
if(ld!=null){
foreach(string name in ld.Values){
oWriter.Write(name.Trim()+",");
}
oWriter.Write(oWriter.NewLine);
}
}
//添加具体数据
foreach(DataRow row in dt.Rows){
foreach(string col in ld.Keys){
string str=row[col].ToString().Trim();
oWriter.Write(str+",");
}
oWriter.Write(oWriter.NewLine);
}
}
}
public static string EndWrite(){
oWriter.Flush();
oWriter.Close();
oStream.Close();
return strCurFileName;
}
#endregion
#region 使用StreamReader将CVS文件转化为一个DataTable
public static DataTable CSVToDataTable(string fn){
FileStream stream=File.Open(
fn,
FileMode.Open);
var reader=new StreamReader(
stream,
Encoding.GetEncoding("GB2312"));
string line="";
var dt=new DataTable("sadf");
while((line=reader.ReadLine())!=null){
string[] values=line.Split(',');
int count=dt.Columns.Count;
int addNum=values.Length-count;
if(addNum>0){
AddColumn(
dt,
addNum);
}
DataRow row=dt.NewRow();
for(int num=0;
num<values.Length;
num++){
row[num]=values[num];
}
dt.Rows.Add(row);
}
reader.Close();
stream.Close();
return dt;
}
public static void AddColumn(DataTable dt,int addNum){
int count=dt.Columns.Count;
for(int num=count-1;
num<count+addNum;
num++){
int colIndex=num+1;
var col=new DataColumn(
colIndex.ToString(),
typeof(string));
dt.Columns.Add(col);
}
}
#endregion
}
}
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
namespace ExcelObject{
internal class ExcelHelper{
private static FileStream oStream; //当前文件流
private static StreamWriter oWriter; //当前输出流
private static string strCurFileName; //使用组合函数输出Excel文件时使用的文件名
public static string GetExcelConnectionStr(string fileName){
return "provider=Microsoft.Jet.OLEDB.4.0"+";data source="+fileName
+";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
}
public static string GetTextConnectionStr(string fileName){
string strPath=Path.GetDirectoryName(fileName);
return "Provider=Microsoft.Jet.OLEDB.4.0"+";Data Source="+strPath
+";Extended Properties=\"text;HDR=Yes;IMEX=1;\""; //FMT=Delimited;
}
public static DataSet GetDBFromTextFile(string fn){
string fileName=Path.GetFileName(fn);
string strSql="SELECT * From "+fileName;
string connStr=GetTextConnectionStr(fn);
var ds=new DataSet("DataSet1");
var oAdap=new OleDbDataAdapter(
strSql,
connStr);
oAdap.Fill(ds);
return ds;
}
public static DataSet GetDBFromExcel(string fileName,string strSql){
var ds=new DataSet("DataSet1");
string connStr=GetExcelConnectionStr(fileName);
var adap=new OleDbDataAdapter(
strSql,
connStr);
adap.Fill(ds);
return ds;
}
public static DataSet GetDBFromExcel(string fileName){
var ds=new DataSet("DataSet1");
int num=1;
while(true){
try{
string strSql="SELECT * FROM [Sheet"+num+"$]";
DataSet ds2=GetDBFromExcel(
fileName,
strSql);
ds2.Tables[0].TableName="Table"+num;
UpdateTableColumn(ds2.Tables[0]);
ds.Merge(ds2);
num++;
}
catch{
break;
}
}
//清除数据
ClearNullData(ref ds);
return ds;
}
private static void UpdateTableColumn(DataTable dt){
if(dt==null){
return;
}
foreach(DataColumn col in dt.Columns){
col.ColumnName=col.ColumnName.Trim();
}
}
private static void ClearNullData(ref DataSet ds){
if(ds==null){
return ;
}
foreach(DataTable dt in ds.Tables){
//清空错误的列
if(dt.Rows.Count==0){
continue;
}
//清空错误的行
var alRow=new ArrayList();
foreach(DataRow row in dt.Rows){
if(IsNull(row)){
alRow.Add(row);
}
}
foreach(DataRow row in alRow){
dt.Rows.Remove(row);
}
}
return ;
}
private static bool IsNull(DataRow row){
object[] items=row.ItemArray;
bool isNull=true;
foreach(object item in items){
if(item!=null
&&item.ToString().Trim()!=""){
isNull=false;
break;
}
}
return isNull;
}
public static string GetTempDirectory(){
string directory=ConfigurationSettings.AppSettings["TempDirectory"];
if(!Directory.Exists(directory)){
Directory.CreateDirectory(directory);
}
return directory;
}
public static string GetTemplateDirectory(){
string directory=ConfigurationSettings.AppSettings["TemplateDirectory"];
return directory;
}
public static string GetTempFileName(string extName) {return GetTempDirectory()+@"\"+Math.Abs(Guid.NewGuid().ToString().GetHashCode())+extName;}
public static string GetTempFileName(string directory,string extName) {return directory+@"\"+Guid.NewGuid()+"."+extName;}
public static string SaveTempFile(HtmlInputFile fileUp){
string extName=Path.GetExtension(fileUp.PostedFile.FileName);
string tempFile=GetTempFileName(extName);
fileUp.PostedFile.SaveAs(tempFile);
return tempFile;
}
public static DataSet GetTempFileDB(HtmlInputFile fileUp){
//保存临时文件
string fn=SaveTempFile(fileUp);
DataSet ds=null;
//获取数据
if(IsTextFile(fn)){
ds=new DataSet("DataSet1");
DataTable dt=CSVToDataTable(fn);
dt.Rows.RemoveAt(0);
UpdateTableColumn(dt);
ds.Tables.Add(dt);
}
else{
ds=GetDBFromExcel(fn);
}
return ds;
}