c# 对datagriview导出excel 导出CSV 读写EXCEL

最新推荐文章于 2024-08-03 16:17:47 发布

qq_739246247

最新推荐文章于 2024-08-03 16:17:47 发布

阅读量461

点赞数

private void setExcel(DataGridView dgv, string name)
{
//总可Di见列C数，A总可Di见行a数
int colCount = dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible);
int rowCount = dgv.Rows.GetRowCount(DataGridViewElementStates.Visible);
//dataGridView 没有3数据u提¦¢G示DU
//if (dgv.Rows.Count == 0 || rowCount == 0)
//{
// MessageBox.Show("表i中?沒LS有3數A據U", "提¦¢G示DU");
//}
//else
//{
//选择创建O文a件Do的o路Mo径
SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel files(*.xls)|*.xls";
save.Title = "請D選i擇U要n匯¡Ñ出DX數A據U的o位i置Mm";
save.FileName = name + DateTime.Now.ToLongDateString();
if (save.ShowDialog() == DialogResult.OK)
{
string fileName = save.FileName;
// 创建OExcel对象H
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (excel == null)
{
MessageBox.Show("Excel無gL法k啟ÓO動XE", "提¦¢G示DU");
return;
}
//创建OExcel工u作±@薄!
Microsoft.Office.Interop.Excel.Workbook excelBook = excel.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets[1];

//excel.Application.Workbooks.Add(true);
//生DI成¡L字r段q名W称
int k = 0;
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].Visible) //不¢G导出DX隐藏A的o列C
{
excel.Cells[1, k + 1] = dgv.Columns[i].HeaderText;
k++;
}
}
//填n充DR数据u
for (int i = 0; i < dgv.RowCount; i++)
{
k = 0;
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv.Columns[j].Visible) //不¢G导出DX隐藏A的o列C
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, k + 1] = "'" + dgv[j, i].Value;
}
else
{
excel.Cells[i + 2, k + 1] = dgv[j, i].Value;
}
}
k++;
}
}
try
{
//自U动调整a列C宽
Microsoft.Office.Interop.Excel.Range allColumn = excelSheet.Columns;
allColumn.AutoFit();
Microsoft.Office.Interop.Excel.Range range = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[dgv.RowCount + 1, dgv.ColumnCount]];
range.Borders.LineStyle = 1;
excelBook.Saved = true;
excelBook.SaveCopyAs(fileName);
//KillProcess("EXCEL");
MessageBox.Show("文件導出成功");
}
catch
{
MessageBox.Show("導出失败", "提示");
}

}

}

导出CSV

public void SaveCSV(DataTable dt, string fileName)
{
FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
string data = "";

//写出列名称
for (int i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);

//写出各行数据
for (int i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
data += dt.Rows[i][j].ToString();
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
}

sw.Close();
fs.Close();
MessageBox.Show("CSV文件保存成功！");

}

读Excel

public static DataSet ReadFile(string path, string name)
{
if (string.IsNullOrWhiteSpace(path) || string.IsNullOrWhiteSpace(name) || !File.Exists(path + "\\" + name))
return null;
// ?取excel
string connstring = string.Empty;
string strSql = string.Empty;
if (name.EndsWith(".xls") || name.EndsWith(".xlsx"))
{
connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "\\" + name + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
strSql = "select * from [工作表1$]";

}
// ?取csv文件
//if (name.EndsWith(".csv"))
//{
// connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='text;HDR=YES;FMT=Delimited';";
// strSql = "select * from " + name;
//}
else
{
return null;
}
DataSet ds = null;
OleDbConnection conn = null;
try
{
conn = new OleDbConnection(connstring);
conn.Open();
OleDbDataAdapter myCommand = null;

myCommand = new OleDbDataAdapter(strSql, connstring);
ds = new DataSet();
myCommand.Fill(ds, "工作表1$");
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}
return ds;
}

填写Excel模板

#region 打開EXCEL模板
//string savefileName = AppDomain.CurrentDomain.BaseDirectory.ToString() + "export\\" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + "AOI_SPI CT.xlsx";

string savefileName = AppDomain.CurrentDomain.BaseDirectory.ToString() + "export\\"+ "AOI_SPI CT.xlsx";
string openfileName = AppDomain.CurrentDomain.BaseDirectory.ToString() + "template\\AOI_SPI CT.xlsx";

KillProcess("EXCEL");
object missing = Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例Excel类
if (excel == null)
{
errorMSG("Excel無法啟動!");
return;
}
//appExcel.DisplayAlerts = false;//DisplayAlerts 属性设置成 False，就不会出现这种警告。
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(openfileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);//打开Excel
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;//实例表格
//excel.Visible = false;

excel.Visible = true;
#endregion

try
{

FileInfo[] a = GetFiles(AppDomain.CurrentDomain.BaseDirectory.ToString() + "export");
if (a.Length > 0)
{
timer1.Stop();
for (int i = 0; i < a.Length; i++)
{
FileInfo xFile = a[i];
string FileString = FileHelper.ShareRead(xFile.FullName, Encoding.ASCII);


if (File.GetAttributes(xFile.FullName) != FileAttributes.Normal)
{
File.SetAttributes(xFile.FullName, FileAttributes.Normal);
}

xFile.Delete();

}

}

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[6];//第一个表格
worksheet.Activate();
FillTable(iEcolumnStart, dt.Rows.Count, iETitleRow, dt, excel);

workbook.Saved = true;
workbook.SaveCopyAs(savefileName);
KillProcess("EXCEL");
//MessageBox.Show("文件導出成功", "提示");
}
catch
{
MessageBox.Show("導出失敗，文件可能正在使用中", "提示");
}