C#在C/S下如何将DataGridView导出Word或Excel?
C#在C/S下如何将DataGridView导出Word或Excel?
本人新手，最好有详细代码，谢谢！
[解决办法]
using Excel;
在项目中引入Excel.dll
/// <summary> /// 导出Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExportExcel_Click(object sender, EventArgs e) { DataTable dt = this.dgvWaterTicket.DataSource; if (dt == null) { return; } if (dt.Rows.Count == 0) { return; } Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("请确保您的电脑已经安装Excel", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } xlApp.UserControl = true; Excel.Workbooks workbooks = xlApp.Workbooks; //根据模版产生新的workbook //Workbook workbook = workbooks.Add("D:\\aa.xls"); Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 if (worksheet == null) { MessageBox.Show("请确保您的电脑已经安装Excel", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; worksheet.Cells[1, 1] = frm.Text;//导出的标题 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, dt.]).MergeCells = true; //合并单元格---列数 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中对齐 worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]).ColumnWidth = 15; //列宽 worksheet.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 2]).ColumnWidth = 15; //列宽 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).ColumnWidth = 20; //列宽 //写入字段 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i]; } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; //System.Threading.Thread.Sleep(500); //如果字的数量过多则自动换行。worksheet.Cells[r+1, 4]为worksheet.Cells[行, 列] worksheet.get_Range(worksheet.Cells[r + 3, 4], worksheet.Cells[r + 1, 4]).Columns.WrapText = true; //自动换行 worksheet.get_Range(worksheet.Cells[r + 3, 4], worksheet.Cells[r + 3, 4]).Rows.AutoFit(); //自动加行高 //this.Text = "导出数据[" + percent.ToString("0.00") + "%]..."; } range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } xlApp.Visible = true; } catch { MessageBox.Show("到出Excel失败！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); //KillProcess("Excel"); GC.Collect();//强行销毁 } }
[解决办法]
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);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_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 - 1; 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;
}
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
}
finally
{
app.UserControl = false;
app.Quit();
}
return true;
}
private void SaveAs()
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true
saveFileDialog.Title = "";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgv.Columns[i].HeaderText;
}
sw.WriteLine(str);
for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgv.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dgv.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
Excel.Application myexcel = new Excel.Application();
Excel.Workbook mybook = default(Excel.Workbook);
Excel.Worksheet mysheet = default(Excel.Worksheet);
mybook = myexcel.Workbooks.Add("ExcelFilename");
mysheet = mybook.Worksheets("sheet1");
mysheet.Activate();
mysheet.Range("A1").Select();
DataGridView.SelectAll();
System.Windows.Forms.DataObject t = new System.Windows.Forms.DataObject();
t = dv.GetClipboardContent();
System.Windows.Forms.Clipboard.SetDataObject(t);
mysheet.PasteSpecial(Format = "文本", Link = false, DisplayAsIcon = false);