asp.net中怎么把gridview中的数据写入一个文件，再保存到桌面 还有文件中的数据怎么上传到gridview中
using System;
using System.Web;
using System.Web.UI;
using System.IO;
using System.Web.UI.WebControls;
namespace DotNet.Utilities
{
public class ExportExcel
{
protected void ExportData(string strContent, string FileName)
{
FileName = FileName + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
//this.Page.EnableViewState = false;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls");
// 把文件流发送到客户端
HttpContext.Current.Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
HttpContext.Current.Response.Write(strContent);
HttpContext.Current.Response.Write("</body></html>");
// 停止页面的执行
//Response.End();
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="obj"></param>
public void ExportData(GridView obj)
{
try
{
string style = "";
if (obj.Rows.Count > 0)
{
style = @"<style> .text { mso-number-format:\@; } </script> ";
}
else
{
style = "no data.";
}
HttpContext.Current.Response.ClearContent();
DateTime dt = DateTime.Now;
string filename = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=ExportData" + filename + ".xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
obj.RenderControl(htw);
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
catch
{
}
}
}
}
读取EXcel 文件到datagirfdview
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace LVSPaySystem.Como
{
public class ExcelHelperReport
{
///
/// 根据Excel列类型获取列的值
///
/// Excel列
///
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
/// <summary>
/// 自动设置Excel列宽
/// </summary>
/// <param name="sheet">Excel表</param>
private static void AutoSizeColumns(ISheet sheet)
{
if (sheet.PhysicalNumberOfRows > 0)
{
IRow headerRow = sheet.GetRow(0);
for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
{
sheet.AutoSizeColumn(i);
}
}
}
/// <summary>
/// 保存Excel文档流到文件
/// </summary>
/// <param name="ms">Excel文档流</param>
/// <param name="fileName">文件名</param>
private static void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
}
/// <summary>
/// DataReader转换成Excel文档流
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static MemoryStream RenderToExcel(IDataReader reader)
{
MemoryStream ms = new MemoryStream();
using (reader)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
int cellCount = reader.FieldCount;
// handling header.
for (int i = 0; i < cellCount; i++)
{
headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
}
// handling value.
int rowIndex = 1;
while (reader.Read())
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < cellCount; i++)
{
dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
}
rowIndex++;
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return ms;
}
/// <summary>
/// DataReader转换成Excel文档流，并保存到文件
/// </summary>
/// <param name="reader"></param>
/// <param name="fileName">保存的路径</param>
public static void RenderToExcel(IDataReader reader, string fileName)
{
using (MemoryStream ms = RenderToExcel(reader))
{
SaveToFile(ms, fileName);
}
}
#region 自定义颜色
private static short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
{
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
else
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
s = XlColour.GetIndex();
}
}
else
s = XlColour.GetIndex();
return s;
}
Color LevelOneColor = Color.FromArgb(143, 176, 229);
static Color LevelTwoColor = Color.FromArgb(201, 217, 243);
static Color LevelThreeColor = Color.FromArgb(231, 238, 248);
static Color LevelFourColor = Color.FromArgb(232, 230, 231);
static Color LevelFiveColor = Color.FromArgb(250, 252, 213);
/// <summary>
/// 分层设置单元格样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="alignment"></param>
/// <param name="valingment"></param>
/// <returns></returns>
public static HSSFCellStyle SetStyle(HSSFWorkbook workbook, short alignment, short valingment, int layer)
{
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
//style.Alignment = alignment;
//style.VerticalAlignment = valingment;
//style.BorderBottom = HSSFCellStyle.BORDER_THIN;
//style.BorderLeft = HSSFCellStyle.BORDER_THIN;
//style.BorderRight = HSSFCellStyle.BORDER_THIN;
//style.BorderTop = HSSFCellStyle.bo;
switch (layer)
{
//case 0:
// style.FillForegroundColor = GetXLColour(workbook, LevelOneColor); //调用GetXLColour方法
// // style.FillPattern = HSSFCellStyle.ALT_BARS;
// style.FillBackgroundColor = GetXLColour(workbook, LevelOneColor);
// break;
case 1:
style.FillForegroundColor = GetXLColour(workbook, LevelTwoColor);
//style.FillPattern = HSSFCellStyle.ALT_BARS;
style.FillBackgroundColor = GetXLColour(workbook, LevelTwoColor);
break;
default:
break;
}
return style;
}
#endregion
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// dateStyle.FillForegroundColor = (HSSFCellStyle.FillForegroundColor)Color.Red;
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
// dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
string drValue = row[column].ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return ms;
}
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
//public static MemoryStream DatagridviewToExcel(DataGridView myDgv)
//{
// MemoryStream ms = new MemoryStream();
// // using (myDgv)
// // {
// #region
// using (IWorkbook workbook = new HSSFWorkbook())
// {
// using (ISheet sheet = workbook.CreateSheet())
// {
// IRow headerRow = sheet.CreateRow(0);
// // handling header.
// foreach (DataGridViewColumn column in myDgv.Columns)
// headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
// // handling value.
// int rowIndex = 1;
// HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
// dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
// foreach (DataGridViewRow row in myDgv.Rows)
// {
// IRow dataRow = sheet.CreateRow(rowIndex);
// if (row.Index > 0)
// {
// #region column
// foreach (DataGridViewColumn column in myDgv.Columns)
// {
// // dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
// // string drValue = row[column].ToString(); row.Cells[column.Index];
// // string drValue = myDgv[column.Index, row.Index==0?1:row.Index-1].Value.ToString();
// string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
// HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
// switch (column.ValueType.ToString())
// {
// case "System.String"://字符串类型
// newCell.SetCellValue(drValue);
// break;
// case "System.DateTime"://日期类型
// DateTime dateV;
// DateTime.TryParse(drValue, out dateV);
// newCell.SetCellValue(dateV);
// newCell.CellStyle = dateStyle;//格式化显示
// break;
// case "System.Boolean"://布尔型
// bool boolV = false;
// bool.TryParse(drValue, out boolV);
// newCell.SetCellValue(boolV);
// break;
// case "System.Int16"://整型
// case "System.Int32":
// case "System.Int64":
// case "System.Byte":
// int intV = 0;
// int.TryParse(drValue, out intV);
// newCell.SetCellValue(intV);
// break;
// case "System.Decimal"://浮点型
// case "System.Double":
// double doubV = 0;
// double.TryParse(drValue, out doubV);
// newCell.SetCellValue(doubV);
// break;
// case "System.DBNull"://空值处理
// newCell.SetCellValue("");
// break;
// default:
// newCell.SetCellValue("");
// break;
// }
// }
// #endregion
// }
// else
// {
// rowIndex--;
// }
// rowIndex++;
// }
// AutoSizeColumns(sheet);
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// }
// }
// // }
// #endregion
// return ms;
//}
/// <summary>
/// DataTable转换成Excel文档流，并保存到文件
/// </summary>
/// <param name="table"></param>
/// <param name="fileName">保存的路径</param>
public static string DataTableToExcel(DataTable table, string fileName)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
try
{
using (MemoryStream ms = RenderToExcel(table))
{
SaveToFile(ms, fileName);
}
}
catch (Exception ex)
{
return ex.ToString();
}
return "";
}
// <summary>
/// DataGridview转换成Excel文档流，并保存到文件
/// </summary>
/// <param name="myDgv"><DataGridview/param>
/// <param name="myDgv"></param>
/// <param name="fileName">保存的路径及文件名</param>
//public static void DatagridviewToExcel(DataGridView myDgv, string fileName)
//{
// using (MemoryStream ms = DatagridviewToExcel(myDgv))
// {
// SaveToFile(ms, fileName);
// }
//}
/// <summary>
/// Excel文档流是否有数据
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <returns></returns>
public static bool HasData(Stream excelFileStream)
{
return HasData(excelFileStream, 0);
}
/// <summary>
/// Excel文档流是否有数据
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetIndex">表索引号，如第一个表为0</param>
/// <returns></returns>
public static bool HasData(Stream excelFileStream, int sheetIndex)
{
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
if (workbook.NumberOfSheets > 0)
{
if (sheetIndex < workbook.NumberOfSheets)
{
using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
{
return sheet.PhysicalNumberOfRows > 0;
}
}
}
}
}
return false;
}
/// <summary>
/// Excel文档流转换成DataTable
/// 第一行必须为标题行
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetName">表名称</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName)
{
return RenderFromExcel(excelFileStream, sheetName, 0);
}
/// <summary>
/// Excel文档流转换成DataTable
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetName">表名称</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
{
DataTable table = null;
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheet(sheetName))
{
table = RenderFromExcel(sheet, headerRowIndex);
}
}
}
return table;
}
/// <summary>
/// Excel文档流转换成DataTable
/// 默认转换Excel的第一个表
/// 第一行必须为标题行
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream)
{
return RenderFromExcel(excelFileStream, 0, 0);
}
/// <summary>
/// Excel文档流转换成DataTable
/// 第一行必须为标题行
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetIndex">表索引号，如第一个表为0</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex)
{
return RenderFromExcel(excelFileStream, sheetIndex, 0);
}
/// <summary>
/// Excel文档流转换成DataTable
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="sheetIndex">表索引号，如第一个表为0</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
{
DataTable table = null;
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
{
table = RenderFromExcel(sheet, headerRowIndex);
}
}
}
return table;
}
/// <summary>
/// Excel表格转换成DataTable
/// </summary>
/// <param name="sheet">表格</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
private static DataTable RenderFromExcel(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
}
return table;
}
/// <summary>
/// Excel文档导入到数据库
/// 默认取Excel的第一个表
/// 第一行必须为标题行
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="insertSql">插入语句</param>
/// <param name="dbAction">更新到数据库的方法</param>
/// <returns></returns>
public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction)
{
return RenderToDb(excelFileStream, insertSql, dbAction, 0, 0);
}
public delegate int DBAction(string sql, params IDataParameter[] parameters);
/// <summary>
/// Excel文档导入到数据库
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="insertSql">插入语句</param>
/// <param name="dbAction">更新到数据库的方法</param>
/// <param name="sheetIndex">表索引号，如第一个表为0</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction, int sheetIndex, int headerRowIndex)
{
int rowAffected = 0;
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
{
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
builder.Append(insertSql);
builder.Append(" values (");
for (int j = row.FirstCellNum; j < cellCount; j++)
{
builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));
}
builder.Length = builder.Length - 1;
builder.Append(");");
}
if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
{
//每50条记录一次批量插入到数据库
rowAffected += dbAction(builder.ToString());
builder.Length = 0;
}
}
}
}
}
return rowAffected;
}
}
}
http://www.cnblogs.com/sufei/archive/2010/03/28/1698923.html前段时间看到的，学习中