1.首先是如何将数据从 xls文件加载到winform的DataGridView控件中。
- private void button1_Click(object sender, EventArgs e)
- {
- OpenFileDialog fileDLG = new OpenFileDialog();
- fileDLG.Title = "Open Excel File";
- fileDLG.Filter = "Excel Files|*.xls;*.xlsx";
- fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";
- if (fileDLG.ShowDialog() == DialogResult.OK)
- {
- string filename = System.IO.Path.GetFileName(fileDLG.FileName);
- string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);
- excelLocationTB.Text = @path + "\\" + filename;
- string ExcelFile = @excelLocationTB.Text;
- if (!File.Exists(ExcelFile))
- MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));
- OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");
- theConnection.Open();
- OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);
- DataSet DS = new DataSet();
- theDataAdapter.Fill(DS, "ExcelInfo");
- dataGridView1.DataSource = DS.Tables["ExcelInfo"];
- formatDataGrid();
- MessageBox.Show("Excel File Loaded");
- toolStripProgressBar1.Value += 0;
- }
- }
- private void formatDataGrid()
- {
- dataGridView1.ColumnHeadersVisible = true;
- dataGridView1.Columns[0].Name = "Path Name";
- dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
- }
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fileDLG = new OpenFileDialog();
fileDLG.Title = "Open Excel File";
fileDLG.Filter = "Excel Files|*.xls;*.xlsx";
fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";
if (fileDLG.ShowDialog() == DialogResult.OK)
{
string filename = System.IO.Path.GetFileName(fileDLG.FileName);
string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);
excelLocationTB.Text = @path + "\\" + filename;
string ExcelFile = @excelLocationTB.Text;
if (!File.Exists(ExcelFile))
MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));
OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");
theConnection.Open();
OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);
DataSet DS = new DataSet();
theDataAdapter.Fill(DS, "ExcelInfo");
dataGridView1.DataSource = DS.Tables["ExcelInfo"];
formatDataGrid();
MessageBox.Show("Excel File Loaded");
toolStripProgressBar1.Value += 0;
}
}
private void formatDataGrid()
{
dataGridView1.ColumnHeadersVisible = true;
dataGridView1.Columns[0].Name = "Path Name";
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
2.接下来如何将DataGridView控件中的数据保存到CSV格式的文件中。
- if (dataGridView1.Rows.Count == 0)
- {
- MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
- else
- {
- SaveFileDialog saveFileDialog = new SaveFileDialog();
- saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
- saveFileDialog.FilterIndex = 0;
- saveFileDialog.RestoreDirectory = true;
- saveFileDialog.CreatePrompt = true;
- saveFileDialog.FileName = null;
- saveFileDialog.Title = "Save path of the file to be exported";
- if (saveFileDialog.ShowDialog() == DialogResult.OK)
- {
- Stream myStream = saveFileDialog.OpenFile();
- StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
- string strLine = "";
- try
- {
- //Write in the headers of the columns.
- for (int i = 0; i < dataGridView1.ColumnCount; i++)
- {
- if (i > 0)
- strLine += ",";
- strLine += dataGridView1.Columns[i].HeaderText;
- }
- strLine.Remove(strLine.Length - 1);
- sw.WriteLine(strLine);
- strLine = "";
- //Write in the content of the columns.
- for (int j = 0; j < dataGridView1.Rows.Count; j++)
- {
- strLine = "";
- for (int k = 0; k < dataGridView1.Columns.Count; k++)
- {
- if (k > 0)
- strLine += ",";
- if (dataGridView1.Rows[j].Cells[k].Value == null)
- strLine += "";
- else
- {
- string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
- strLine += m.Replace(",", ",");
- }
- }
- strLine.Remove(strLine.Length - 1);
- sw.WriteLine(strLine);
- //Update the Progess Bar.
- toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
- }
- sw.Close();
- myStream.Close();
- MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
- toolStripProgressBar1.Value = 0;
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "Save path of the file to be exported";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string strLine = "";
try
{
//Write in the headers of the columns.
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
strLine += ",";
strLine += dataGridView1.Columns[i].HeaderText;
}
strLine.Remove(strLine.Length - 1);
sw.WriteLine(strLine);
strLine = "";
//Write in the content of the columns.
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
strLine = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
strLine += ",";
if (dataGridView1.Rows[j].Cells[k].Value == null)
strLine += "";
else
{
string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
strLine += m.Replace(",", ",");
}
}
strLine.Remove(strLine.Length - 1);
sw.WriteLine(strLine);
//Update the Progess Bar.
toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
}
sw.Close();
myStream.Close();
MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
toolStripProgressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
3.接下是如何保存到XML,这里提供了两种方法。
- Output to XML file format using StreamWriting Object.
- if (dataGridView1.Rows.Count == 0)
- {
- MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
- else
- {
- SaveFileDialog saveFileDialog = new SaveFileDialog();
- saveFileDialog.Filter = "XML files (*.xml)|*.xml";
- saveFileDialog.FilterIndex = 0;
- saveFileDialog.RestoreDirectory = true;
- saveFileDialog.CreatePrompt = true;
- saveFileDialog.FileName = null;
- saveFileDialog.Title = "Save path of the file to be exported";
- if (saveFileDialog.ShowDialog() == DialogResult.OK)
- {
- Stream myStream = saveFileDialog.OpenFile();
- StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
- try
- {
- sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
- sw.WriteLine("<NewXML>");
- for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
- {
- sw.WriteLine("<Row" + i + ">");
- for (int j = 0; j < dataGridView1.Columns.Count; j++)
- sw.WriteLine("<" + dataGridView1.Columns[j].HeaderText + ">" + dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() + "</" + dataGridView1.Columns[j].HeaderText + ">");
- sw.WriteLine("</Row" + i + ">");
- //Update the Progess Bar.
- toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);
- }
- sw.WriteLine("</NewXML>");
- sw.Close();
- myStream.Close();
- MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
- toolStripProgressBar1.Value = 0;
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
Output to XML file format using StreamWriting Object.
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XML files (*.xml)|*.xml";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "Save path of the file to be exported";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
try
{
sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
sw.WriteLine("<NewXML>");
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
sw.WriteLine("<Row" + i + ">");
for (int j = 0; j < dataGridView1.Columns.Count; j++)
sw.WriteLine("<" + dataGridView1.Columns[j].HeaderText + ">" + dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() + "</" + dataGridView1.Columns[j].HeaderText + ">");
sw.WriteLine("</Row" + i + ">");
//Update the Progess Bar.
toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);
}
sw.WriteLine("</NewXML>");
sw.Close();
myStream.Close();
MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
toolStripProgressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
上面是hard code写法,用XMLELEMENT也能达到同样目的。
- if (dataGridView1.Rows.Count == 0)
- {
- MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
- else
- {
- SaveFileDialog saveFileDialog = new SaveFileDialog();
- saveFileDialog.Filter = "XML files (*.xml)|*.xml";
- saveFileDialog.FilterIndex = 0;
- saveFileDialog.RestoreDirectory = true;
- saveFileDialog.CreatePrompt = true;
- saveFileDialog.FileName = null;
- saveFileDialog.Title = "Save path of the file to be exported";
- if (saveFileDialog.ShowDialog() == DialogResult.OK)
- {
- string str = saveFileDialog.FileName;
- XmlDocument doc = new XmlDocument();
- XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
- doc.AppendChild(docNode);
- // Create and insert a new element.
- XmlNode rowsNode = doc.CreateElement("NewXML");
- doc.AppendChild(rowsNode);
- try
- {
- for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
- {
- XmlNode rowNode = doc.CreateElement("row" + i);
- for (int j = 0; j < dataGridView1.Columns.Count; j++)
- {
- XmlAttribute rowAttribute = doc.CreateAttribute(dataGridView1.Columns[j].HeaderText);
- rowAttribute.Value =dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() ;
- rowNode.Attributes.Append(rowAttribute);
- rowsNode.AppendChild(rowNode);
- }
- //Update the Progess Bar.
- toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);
- }
- doc.Save(str);
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XML files (*.xml)|*.xml";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "Save path of the file to be exported";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string str = saveFileDialog.FileName;
XmlDocument doc = new XmlDocument();
XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
doc.AppendChild(docNode);
// Create and insert a new element.
XmlNode rowsNode = doc.CreateElement("NewXML");
doc.AppendChild(rowsNode);
try
{
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
XmlNode rowNode = doc.CreateElement("row" + i);
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
XmlAttribute rowAttribute = doc.CreateAttribute(dataGridView1.Columns[j].HeaderText);
rowAttribute.Value =dataGridView1.Rows[i].Cells[j].Value.ToString().Trim() ;
rowNode.Attributes.Append(rowAttribute);
rowsNode.AppendChild(rowNode);
}
//Update the Progess Bar.
toolStripProgressBar1.Value = 100 * (i + 1) / (dataGridView1.Rows.Count - 1);
}
doc.Save(str);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
3.最后提供保存到EXCEL的三种方法,第一种是hardcode,第二种是openxml pacage,第三种是传统的操作Excl interp的貌似是 Automatic方法。
- if (dataGridView1.Rows.Count == 0)
- {
- MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return;
- }
- else
- {
- SaveFileDialog saveFileDialog = new SaveFileDialog();
- saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
- saveFileDialog.FilterIndex = 0;
- saveFileDialog.RestoreDirectory = true;
- saveFileDialog.CreatePrompt = true;
- saveFileDialog.FileName = null;
- saveFileDialog.Title = "Save path of the file to be exported";
- if (saveFileDialog.ShowDialog() == DialogResult.OK)
- {
- Stream myStream = saveFileDialog.OpenFile();
- StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
- string strLine = "";
- try
- {
- //Write in the headers of the columns.
- for (int i = 0; i < dataGridView1.ColumnCount; i++)
- {
- if (i > 0)
- strLine += Convert.ToChar(9);
- strLine += dataGridView1.Columns[i].HeaderText;
- }
- strLine.Remove(strLine.Length - 1);
- sw.WriteLine(strLine);
- strLine = "";
- //Write in the content of the columns.
- for (int j = 0; j < dataGridView1.Rows.Count; j++)
- {
- strLine = "";
- for (int k = 0; k < dataGridView1.Columns.Count; k++)
- {
- if (k > 0)
- strLine += Convert.ToChar(9);
- if (dataGridView1.Rows[j].Cells[k].Value == null)
- strLine += "";
- else
- {
- string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
- strLine += m.Replace(",", ",");
- }
- }
- strLine.Remove(strLine.Length - 1);
- sw.WriteLine(strLine);
- //Update the Progess Bar.
- toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
- }
- sw.Close();
- myStream.Close();
- MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
- toolStripProgressBar1.Value = 0;
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("No data available!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.FileName = null;
saveFileDialog.Title = "Save path of the file to be exported";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string strLine = "";
try
{
//Write in the headers of the columns.
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
strLine += Convert.ToChar(9);
strLine += dataGridView1.Columns[i].HeaderText;
}
strLine.Remove(strLine.Length - 1);
sw.WriteLine(strLine);
strLine = "";
//Write in the content of the columns.
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
strLine = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
strLine += Convert.ToChar(9);
if (dataGridView1.Rows[j].Cells[k].Value == null)
strLine += "";
else
{
string m = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
strLine += m.Replace(",", ",");
}
}
strLine.Remove(strLine.Length - 1);
sw.WriteLine(strLine);
//Update the Progess Bar.
toolStripProgressBar1.Value = 100 * (j + 1) / dataGridView1.Rows.Count;
}
sw.Close();
myStream.Close();
MessageBox.Show("Data has been exported to:" + saveFileDialog.FileName.ToString(), "Exporting Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
toolStripProgressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exporting Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
Automatic:
- int iRows = 0;
- int iCols = 0;
- int iTrueCols = 0;
- Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
- Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
- Microsoft.Office.Interop.Excel.Worksheet ws = null;
- if (wb.Worksheets.Count > 0)
- {
- ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
- }
- else
- {
- wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
- ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
- }
- if (ws != null)
- {
- ws.Name = "SheetName";
- iRows = dataGridView1.Rows.Count; //加上列头行
- iTrueCols = dataGridView1.Columns.Count; //包含隐藏的列,一共有多少列
- //求列数,省略Visible = false的列
- for (int i = 0; i < dataGridView1.Columns.Count; i++)
- {
- if (dataGridView1.Columns[i].Visible) iCols++;
- }
- string[,] dimArray = new string[iRows + 1, iCols];
- for (int j = 0, k = 0; j < iTrueCols; j++)
- {
- //省略Visible = false的列
- if (dataGridView1.Columns[j].Visible)
- {
- dimArray[0, k] = dataGridView1.Columns[j].HeaderText;
- k++;
- }
- }
- for (int i = 0; i < iRows; i++)
- {
- for (int j = 0, k = 0; j < iTrueCols; j++)
- {
- //省略Visible = false的列
- if (dataGridView1.Columns[j].Visible)
- {
- if (dataGridView1.Rows[i].Cells[j].Value != null)
- dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();
- else
- dimArray[i + 1, k] = "";
- k++;
- }
- }
- }
- ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;
- ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;
- ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;
- ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;
- //ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;
- for (int j = 0, k = 0; j < iTrueCols; j++)
- {
- //省略Visible = false的列
- if (dataGridView1.Columns[j].Visible)
- {
- ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);
- //ws.Columns.c = datagridview.Columns[j].Width;
- k++;
- }
- }
- }
- app.Visible = true;
int iRows = 0;
int iCols = 0;
int iTrueCols = 0;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet ws = null;
if (wb.Worksheets.Count > 0)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
else
{
wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
if (ws != null)
{
ws.Name = "SheetName";
iRows = dataGridView1.Rows.Count; //加上列头行
iTrueCols = dataGridView1.Columns.Count; //包含隐藏的列,一共有多少列
//求列数,省略Visible = false的列
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (dataGridView1.Columns[i].Visible) iCols++;
}
string[,] dimArray = new string[iRows + 1, iCols];
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
dimArray[0, k] = dataGridView1.Columns[j].HeaderText;
k++;
}
}
for (int i = 0; i < iRows; i++)
{
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
if (dataGridView1.Rows[i].Cells[j].Value != null)
dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();
else
dimArray[i + 1, k] = "";
k++;
}
}
}
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;
ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;
//ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (dataGridView1.Columns[j].Visible)
{
ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);
//ws.Columns.c = datagridview.Columns[j].Width;
k++;
}
}
}
app.Visible = true;
OpenXml:
string myStream = saveFileDialog.FileName;
CreateSpreadsheetWorkbook(myStream,dvtodt(dataGridView1));
string myStream = saveFileDialog.FileName;
CreateSpreadsheetWorkbook(myStream,dvtodt(dataGridView1));
先将DataGridView转换为DataTable,在操作:
- public static void CreateSpreadsheetWorkbook(string filepath, DataTable dt)
- {
- // Create a spreadsheet document by supplying the filepath.
- // By default, AutoSave = true, Editable = true, and Type = xlsx.
- FileInfo fileInfo = new FileInfo(filepath);
- if (fileInfo.Exists)
- fileInfo.Delete();
- SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
- // Add a WorkbookPart to the document.
- WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
- workbookPart.Workbook = new Workbook();
- // Add a WorksheetPart to the WorkbookPart.
- WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
- worksheetPart.Worksheet = new Worksheet(new SheetData());
- // Add a workStylesPart to the workbookPart.
- WorkbookStylesPart workStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
- workStylesPart.Stylesheet = new Stylesheet();
- // Add a Stylesheet to the workStylesPart.
- WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.WorkbookStylesPart;
- Stylesheet styleSheet = workbookStylesPart.Stylesheet;
- // Add Fonts and other some information.
- Fonts fonts = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };
- // Add based font to Fonts.
- Font font = new Font();
- FontSize fontSize = new FontSize() { Val = 11D };
- Color color = new Color() { Theme = (UInt32Value)1U };
- FontName fontName = new FontName() { Val = "Calibri" };
- FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
- FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
- font.Append(fontSize);
- font.Append(color);
- font.Append(fontName);
- font.Append(fontFamilyNumbering);
- font.Append(fontScheme);
- // Add fontSpecial to Fonts.
- Font fontSpecial = new Font();
- Bold bold = new Bold();
- FontSize fontSizeSpecial = new FontSize() { Val = 11D };
- Color colorSpecial = new Color() { Theme = (UInt32Value)1U };
- FontName fontNameSpecial = new FontName() { Val = "Calibri" };
- FontFamilyNumbering fontFamilyNumberingSpecial = new FontFamilyNumbering() { Val = 2 };
- FontScheme fontSchemeSpecial = new FontScheme() { Val = FontSchemeValues.Minor };
- fontSpecial.Append(bold);
- fontSpecial.Append(fontSizeSpecial);
- fontSpecial.Append(colorSpecial);
- fontSpecial.Append(fontNameSpecial);
- fontSpecial.Append(fontFamilyNumberingSpecial);
- fontSpecial.Append(fontSchemeSpecial);
- // Add font and fontSpecial to fonts collection.
- fonts.Append(font);
- fonts.Append(fontSpecial);
- // Fills.When the object is serialized out as xml, its qualified name is x:fills.
- Fills fills = new Fills() { Count = (UInt32Value)2U };
- Fill fill = new Fill();
- // Pattern.When the object is serialized out as xml, its qualified name is x:patternFill.
- PatternFill patternFill = new PatternFill() { PatternType = PatternValues.None };
- fill.Append(patternFill);
- // This patternFillSpecial served for fillSpecial.
- Fill fillSpecial = new Fill();
- PatternFill patternFillSpecial = new PatternFill() { PatternType = PatternValues.Gray125 };
- fillSpecial.Append(patternFillSpecial);
- // Add fill and fillSpecial to fills collection.
- fills.Append(fill);
- fills.Append(fillSpecial);
- // Borders.When the object is serialized out as xml, its qualified name is x:borders.
- Borders borders = new Borders() { Count = (UInt32Value)1U };
- Border border = new Border();
- // Left Border.When the object is serialized out as xml, its qualified name is x:left.
- LeftBorder leftBorder = new LeftBorder();
- // Right Border.When the object is serialized out as xml, its qualified name is x:right.
- RightBorder rightBorder = new RightBorder();
- //Top Border.When the object is serialized out as xml, its qualified name is x:top.
- TopBorder topBorder = new TopBorder();
- // Bottom Border.When the object is serialized out as xml, its qualified name is x:bottom.
- BottomBorder bottomBorder = new BottomBorder();
- // This element specifies the color and line style for the diagonal border(s) of a cell,
- // possibly including diagonally up and diagonally down.
- DiagonalBorder diagonalBorder = new DiagonalBorder();
- // Add leftBorder, rightBorder,topBorder,bottomBorder and diagonalBorder into border.
- border.Append(leftBorder);
- border.Append(rightBorder);
- border.Append(topBorder);
- border.Append(bottomBorder);
- border.Append(diagonalBorder);
- borders.Append(border);
- // This is the critical object when the sample want to bold the cell format.
- CellStyleFormats cellStyleFormats = new CellStyleFormats() { Count = (UInt32Value)1U };
- CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
- // Add cellFormat to cellStyleFormats
- cellStyleFormats.Append(cellFormat);
- CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)2U };
- CellFormat cellFormatSpecial = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
- CellFormat cellFormatAnotherSpecial = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
- cellFormats.Append(cellFormatSpecial);
- cellFormats.Append(cellFormatAnotherSpecial);
- CellStyles cellStyles = new CellStyles() { Count = (UInt32Value)1U };
- CellStyle cellStyle = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
- cellStyles.Append(cellStyle);
- DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)0U };
- TableStyles tableStyles = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
- StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
- StylesheetExtension stylesheetExtension = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
- stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
- stylesheetExtensionList.Append(stylesheetExtension);
- // Add fonts,fills,borders,cellStyleFormats,cellFormats,cellStyles,differentialFormats,tableStyles and stylesheetEntensionList to styleSheet
- styleSheet.Append(fonts);
- styleSheet.Append(fills);
- styleSheet.Append(borders);
- styleSheet.Append(cellStyleFormats);
- styleSheet.Append(cellFormats);
- styleSheet.Append(cellStyles);
- styleSheet.Append(differentialFormats);
- styleSheet.Append(tableStyles);
- styleSheet.Append(stylesheetExtensionList);
- // Save the result.
- styleSheet.Save();
- // Add Sheets to the Workbook.
- Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
- // Append a new worksheet and associate it with the workbook.
- Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
- sheets.Append(sheet);
- string cl = "";
- uint row = 2;
- int index;
- Cell cell;
- // fill data into Excel file
- foreach (DataRow dr in dt.Rows)
- {
- for (int idx = 0; idx < dt.Columns.Count; idx++)
- {
- if (idx >= 26)
- cl = "A" + Convert.ToString(Convert.ToChar(65 + idx - 26));
- else
- cl = Convert.ToString(Convert.ToChar(65 + idx));
- SharedStringTablePart shareStringPart;
- if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
- {
- shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
- }
- else
- {
- shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
- }
- if (row == 2)
- {
- index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart);
- cell = InsertCellInWorksheet(cl, row - 1, worksheetPart);
- cell.CellValue = new CellValue(index.ToString());
- cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
- }
- // Insert the text into the SharedStringTablePart.
- index = InsertSharedStringItem(dr[idx].ToString(), shareStringPart);
- cell = InsertCellInWorksheet(cl, row, worksheetPart);
- cell.CellValue = new CellValue(index.ToString());
- cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
- }
- row++;
- }
- // Save the workbookPart.
- workbookPart.Workbook.Save();
- // Close the document.
- spreadsheetDocument.Close();
- }
- /// <summary>
- /// Insert SharedStringItem object.
- /// </summary>
- private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
- {
- // If the part does not contain a SharedStringTable, create one.
- if (shareStringPart.SharedStringTable == null)
- {
- shareStringPart.SharedStringTable = new SharedStringTable();
- }
- int i = 0;
- // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
- foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
- {
- if (item.InnerText == text)
- {
- return i;
- }
- i++;
- }
- // The text does not exist in the part. Create the SharedStringItem and return its index.
- shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
- shareStringPart.SharedStringTable.Save();
- return i;
- }
- /// <summary>
- /// Insert cell in worksheet.
- /// </summary>
- private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
- {
- Worksheet worksheet = worksheetPart.Worksheet;
- SheetData sheetData = worksheet.GetFirstChild<SheetData>();
- string cellReference = columnName + rowIndex;
- // If the worksheet does not contain a row with the specified row index, insert one.
- Row row;
- if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
- {
- row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
- }
- else
- {
- row = new Row() { RowIndex = rowIndex };
- sheetData.Append(row);
- }
- // If there is not a cell with the specified column name, insert one.
- if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
- {
- return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
- }
- else
- {
- // Cells must be in sequential order according to CellReference.
- // Determine where to insert the new cell.
- Cell refCell = null;
- Cell newCell = new Cell() { CellReference = cellReference };
- row.InsertBefore(newCell, refCell);
- worksheet.Save();
- return newCell;
- }
- }