Question

c#中怎样将gridview中的数据导出excel

Answer 1

两个方法,一个是将datatable转为excel,一个是将DataGridView 导出为excel

Answer 2

有两个方法,一个是将datatable转为excel,一个是将DataGridView 导出为excel
将dataset导出为excel也很简单,遍历下dataset.tables.

呈现乱码的是我为自己的程序定制的,你可以不加.
public static bool tableinfo_to_excel(System.Data.DataTable dt, string fileName, bool isShowExcle)
{
DataSet ds = get_dataset(dt);
if (ds == null)
{
return false;
}
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
}

app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

//workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Worksheet worksheet.
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//_Worksheet worksheet = (_Worksheet)sheets.Item(1);
worksheet.Name = "惆豢";
if (worksheet == null)
{
return false;
}
int i_row = 0;

foreach (DataRow dr in dt.Rows)
{

string sLen = "";
string s = (string)dr["heardtext"];
string[] s_headtext = s.Split(',');
char H = (char)(64 + s_headtext.Length / 26);
char L = (char)(64 + s_headtext.Length % 26);
if (s_headtext.Length < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
i_row += 1;

string sTmp = sLen + i_row.ToString();

Range ranCaption = worksheet.get_Range(sTmp, "A" + i_row);
//ranCaption.Merge(true);

ranCaption.MergeCells = true;
ranCaption.RowHeight = 30;

//ranCaption.Font.Color=3;
ranCaption.Font.Color = 255;
ranCaption.Font.Bold = true;
ranCaption.Font.Size = 14;
ranCaption.Borders.Color = 255;
//ranCaption.Borders.LineStyle = XlLineStyle.xlDouble;
//ranCaption.Borders.Value = -4119;
ranCaption.Borders.Weight = 3;
//ranCaption.HorizontalAlignment=2;
//object lkjsdklf=ranCaption.Orientation;
//object lkjsdklf = ranCaption.Style;
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;//懈笢
//ranCaption.Font.ColorIndex = 16777215;
//ranCaption.Font.ColorIndex = -2000;
//font_my.Bold =true;

//font_my.Background=XlBackground.xlBackgroundOpaque;
// ranCaption.AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatNone, 2, font_my, "Center", true, null, 80);
ranCaption.Value2 = (string)dr["table_name"];
i_row += 1;

sTmp = sLen + i_row.ToString();
ranCaption = worksheet.get_Range(sTmp, "A" + i_row);
ranCaption.Value2 = s_headtext;
ranCaption.Borders.Color = 0;
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Data.DataTable s_dt = ds.Tables[(string)dr["table_name"]];
object[] obj = new object[s_dt.Columns.Count];
//int i_date_index=new int();
foreach (DataRow dr_jkdj in s_dt.Rows)
{
for (int l = 0; l < s_dt.Columns.Count; l++)
{
if (dr_jkdj[l].GetType() == typeof(DateTime))
{
obj[l] = dr_jkdj[l].ToString();

string cell11 = sLen + ((int)(l + 1)).ToString();
string cell21 = "A" + ((int)(l + 1)).ToString();
Range ran1 = worksheet.get_Range(cell11, cell21);
Range cellrange = (Range)ran1.Cells[0, l + 1];
cellrange.ColumnWidth = 14;
}
//蚚誧赻隅砱氝楼
else if (dr_jkdj[l].GetType() == typeof(Boolean))
{
DateTime dt_jl = (DateTime)dr_jkdj[l - 2];
int i_day = (int)dr_jkdj[l - 1];
if (dt_jl.AddDays(i_day) < DateTime.Now)
{
obj[l] = "峎党闭";
}
else
obj[l] = "淏婓峎党";

}
//蚚誧赻隅砱氝楼赋旰

else
obj[l] = dr_jkdj[l];
}

string cell1 = sLen + ((int)(i_row + 1)).ToString();
string cell2 = "A" + ((int)(i_row + 1)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);

//cellrange.Columns=20;
ran.Borders.Color = 0;
i_row++;
ran.Value2 = obj;

}
i_row = i_row + 2;

}

if (!isShowExcle)
{
workbook.Password = "18356771";
}
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
app.UserControl = false;
app.Quit();
return true;

}
catch(Exception ee)
{
app.UserControl = false;
app.Quit();
return false;
}

}

public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle)
{
//膘蕾?????勤砓
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
}

app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

//workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//_Worksheet worksheet.
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//_Worksheet worksheet = (_Worksheet)sheets.Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//腕郔缀珨蹈蹈靡
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//梓枙
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;

//杅擂
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.RowCount; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView[l, r].ValueType == typeof(DateTime))
{
obj[l] = gridView[l, r].Value.ToString();
}
else
{
obj[l] = gridView[l, r].Value;
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//怅湔
if (!isShowExcle)
{
workbook.Password = "18356771";
}
workbook.SaveCopyAs(fileName);
//workbook.SaveAs(fileName, ".xls", "18356771", false, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, false, false, false, false);
workbook.Saved = true;
}
finally
{
//寿敕
app.UserControl = false;
app.Quit();
}
return true;

}