/// <summary>
/// DataTableToExcel 的摘要说明
/// </summary>
public class DataTableToExcel
{
/// <summary>
/// 由 DataSet 导出 Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="FileName"></param>
public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName)
{
HttpResponse resp = HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0;
DataRow[] myRow = dt.Select("");
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
resp.Write(colHeaders);
foreach (DataRow row in myRow)
{
for (i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
/// <summary>
/// 由 DataSet 导出 Excel ，自定义字段名
/// </summary>
/// <param name="dt"></param>
/// <param name="FileName"></param>
public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName,string[] cells)
{
HttpResponse resp = HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0;
DataRow[] myRow = dt.Select("");
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = 0; i < cells.Length; i++)
{
if (i == cells.Length - 1)
{
colHeaders += cells[i].ToString() + "\n";
}
else
{
colHeaders += cells[i].ToString() + "\t";
}
}
resp.Write(colHeaders);
foreach (DataRow row in myRow)
{
for (i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
/// <summary>
/// 下载服务器端的文件到本地
/// </summary>
/// <param name="_Request"> </param>
/// <param name="_Response"> </param>
/// <param name="_fileName"> 目的文件名称 </param>
/// <param name="_fullPath"> 源文件路径 </param>
/// <param name="_speed"> 速度大小(1024000 -> 10k/s) </param>
public static bool DownloadFile(HttpRequest Request, HttpResponse Response,string fileName,string fullPath, long speed)
{
try
{
FileStream myFile = new FileStream(fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
BinaryReader br = new BinaryReader(myFile);
try
{
Response.AddHeader("Accept-Ranges", "bytes");
Response.Buffer = false;
long fileLength = myFile.Length;
long startBytes = 0;
double pack = 10240; //10K bytes
//int sleep = 200; //每秒5次 即5*10K bytes每秒
int sleep = (int)Math.Floor(1000 * pack / speed) + 1;
if (Request.Headers["Range"] != null)
{
Response.StatusCode = 206;
string[] range = Request.Headers["Range"].Split(new char[] { '=', '-' });
startBytes = Convert.ToInt64(range[1]);
}
Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
if (startBytes != 0)
{
//Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
}
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding("GB2312")));
br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
int maxCount = (int)Math.Floor((fileLength - startBytes) / pack) + 1;
for (int i = 0; i < maxCount; i++)
{
if (Response.IsClientConnected)
{
Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
Thread.Sleep(sleep);
}
else
{
i = maxCount;
}
}
}
catch
{
return false;
}
finally
{
br.Close();
myFile.Close();
}
}
catch
{
return false;
}
return true;
}
//*********************** Excel 导入功能 ***************************//
/// <summary>
/// 读取Excel文档中的数据到内存中
/// </summary>
/// <param name="Path"> 文件名称 </param>
/// <returns> 返回一个数据集 </returns>
public static DataSet ExcelToDataSet(string Path)
{
DataSet ds = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [Sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds);
return ds;
}
/// <summary>
/// 将DataSet中的数据插入到SqlServer中
/// </summary>
/// <param name="ds"> 结果集 </param>
/// <param name="TableName"> 表名 </param>
/// <param name="MasterID"> 订单主表ID </param>
public static void DataSetToSqlServer(DataSet ds,string TableName,string MasterID)
{
DataTable dt = new DataTable();
long ID = Shove._Convert.StrToLong(MasterID,0); //主表ID
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string GoodNo = dt.Rows[i][0].ToString();
string GoodNorm = dt.Rows[i][1].ToString();
int Amount = Shove._Convert.StrToInt(dt.Rows[i][2].ToString(),0);
DAL.Procedures.P_DataTableToSqlServer(TableName, ID, GoodNo, GoodNorm, Amount);
}
}
}
}
