将我们的数据列表视图存储到Excel时，Excel工作表会自动保存 [英] Excel Sheet save automatically when storing our datas listview to excel
问题描述
我正在使用C#语言使用Windows窗体，
我有列表视图，当我单击保存按钮时，它包含一些项目，列表视图项将保存到Excel文件.以下是我将listview详细信息保存到Excel的代码.
I am using windows Forms using C# Language,
I have listview, it contains some items when i click the save button the listview items will saves to Excel File. Below is my code of save listview details to Excel.
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
int i = 1;
int j = 1;
foreach (ListViewItem comp in listView_playlist.Items)
{
ws.Cells[i, j] = comp.Text.ToString();
//MessageBox.Show(comp.Text.ToString());
foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
{
ws.Cells[i, j] = drv.Text.ToString();
j++;
}
j = 1;
i++;
}
我的问题:
这是我的问题，当我单击保存"按钮时，列表视图的详细信息出现在Excel页面上，Excel页面处于打开状态.我需要在Excel页面中单击保存"按钮.然后只有它会被保存.
但是，当我单击保存"按钮时，我需要保存默认设置.只需我们需要输入保存文件"对话框的文件名，它将保存我们的本地主机.没有可见的Excel工作表.我该怎么做.
问候
Vasanthakumar
My problem :
Here what my problem is, When i Click the Save button the listview details comes on the Excel page, The Excel page is open stage. I need to click save button in the excel page. Then only it will saved.
But I need to save default when i click the Save button. Just we need to enter the file name of save file dialogue box, it will save our local host. without visible the Excel sheet. How can i do this.
Regards
Vasanthakumar
推荐答案
可以使用WorkBook类的SaveAs方法进行保存.可以通过将Application Visible属性设置为false来使Excel不可见.
SaveAs方法具有许多参数.因为它们是可选的，所以您不能全部提供它们.在下面的代码中，我仅提供了文件名(第一个参数)和文件格式(第二个参数).该示例假定您具有Excel 2007(使用XlFileFormat.xlExcel12格式).
还要注意xla.Quit(); xla = null;陈述.不要忘记这些.否则，Excel将在后台继续运行.
Saving can be done by using the SaveAs method of the WorkBook class. Making Excel invisible can be done by setting the Application Visible property to false.
The SaveAs method has many parameters. Because they are optional you have not the supply them all. In the code below I have only supplied the filename (1st parameter) and the fileformat (2nd parameter). The example assume you have Excel 2007 (using XlFileFormat.xlExcel12 format).
Also take notice of the xla.Quit(); xla = null; statements. Don''t forget these. Otherwise Excel will keep running in the background.
private string GetFileName()
{
string filename = string.Empty;
SaveFileDialog dialog = new SaveFileDialog();
dialog.InitialDirectory = @"C:\"; // change this to your own default location. Save to the root of C:\ is not a good practice.
DialogResult result = dialog.ShowDialog();
if (result == DialogResult.OK)
{
filename = dialog.FileName;
}
return filename;
}
private void SaveWorkbook(Microsoft.Office.Interop.Excel.Workbook wb, string filename)
{
//Remove the extension from the filename. The SaveAs will add the approriate extension it self (at least it does with Excel 2007)
string localfilename = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(filename), System.IO.Path.GetFileNameWithoutExtension(filename));
// If you are sure to override the file
if (System.IO.File.Exists(localfilename))
System.IO.File.Delete(localfilename);
wb.SaveAs(localfilename, XlFileFormat.xlExcel12); //generates a xlsb file (Excel 2007)
//wb.SaveAs(myfilename, XlFileFormat.xlExcel9795); //generates a excel 97 xls file
}
private void SaveButton_Click(object sender, EventArgs e)
{
string filename = GetFileName();
//Exit code if no file was selected
if (string.IsNullOrEmpty(filename))
return;
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = false; //Change to false so Excel isn't visible
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
int i = 1;
int j = 1;
foreach (ListViewItem comp in listView_playlist.Items)
{
ws.Cells[i, j] = comp.Text.ToString();
//MessageBox.Show(comp.Text.ToString());
foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
{
ws.Cells[i, j] = drv.Text.ToString();
j++;
}
j = 1;
i++;
}
SaveWorkbook(wb, filename);
//Close Excel (don't forget this otherwise Excel will keep running in memory)
xla.Quit();
xla = null;
MessageBox.Show(string.Format("Data is save to {0}", filename));
}
(改进)
CSV的优点是您可以在任何系统上使用它.为了帮助您，我编写了一些代码来展示如何从CSV文件保存和加载.这些示例是非常基本的，并作了一些假设.它会给您一个开始.如果您想了解有关CSV文件的更多信息(例如，使用第一行存储字段名称，或使其更可靠)，则可以在CodeProject文章中搜索CSV.这里有som can文章.基本和高级.
要将播放列表保存为CSV，您可以使用以下代码.要使示例正常工作，您需要
(improvement)
CSV has the advantage that you could use it on any system. To help you I have made some code to show how to save and load from a CSV file. The examples are quite basic and make some assumption. It will give you a start. If you want to learn more about CSV file (for example using the first line to store the field names, or making it more robust) you could search the CodeProject articles for CSV. There are som could articles here. Both basic and advanced.
To save your playlist to a CSV you could use the following code. To make the example work you need
using System.IO;
using System.Text.RegularExpressions;
private void NewSaveButton_Click(object sender, EventArgs e)
{
string filename = GetFileName();
//Exit code if no file was selected
if (string.IsNullOrEmpty(filename))
return;
//make sure there is an extension. Because the GetFileName method doesn't do this
if (Path.GetExtension(filename).Length == 0)
filename = filename + ".csv";
WriteListViewToCsv(listView_playlist, filename, '|');
}
private void WriteListViewToCsv(ListView listview, string csvFilename, char seperator)
{
int i = 1;
int j = 1;
// The using clause will dispose the StreamWriter after use. Because a streamwriter is unmanaged code you should always dispose it
using (StreamWriter sw = new StreamWriter(csvFilename, false))
{
foreach (ListViewItem comp in listview.Items)
{
//sw.Write(comp.Text.ToString()); Don't need to save the item itself as the first subitem is the same as the item.
bool firstItem = true;
foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
{
if (firstItem)
firstItem = false;
else
sw.Write(seperator);
sw.Write(drv.Text.ToString());
j++;
}
sw.WriteLine(); // Write an enter to the list
j = 1;
i++;
}
sw.Flush(); //Make sure
sw.Close();
}
}
要从CSV文件读取保存的播放列表，您可以使用以下代码.它使用正则表达式来分隔行.
To read the saved playlist from the CSV file you could use the follow code. It uses regex to split the line.
private void NewLoad_Click(object sender, EventArgs e)
{
string filename = OpenFileName();
if (string.IsNullOrEmpty(filename))
return;
ImportCSV(listView_playlist, filename, '|');
}
private string OpenFileName()
{
string filename = string.Empty;
OpenFileDialog dialog = new OpenFileDialog();
dialog.InitialDirectory = @"C:\"; // change this to your own default location. Save to the root of C:\ is not a good practice.
DialogResult result = dialog.ShowDialog();
if (result == DialogResult.OK)
{
filename = dialog.FileName;
}
return filename;
}
private void ImportCSV(ListView listview, string filename, char seperator)
{
using (StreamReader sr = new StreamReader(filename))
{
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
// split the line into seperate values
string[] values = Regex.Split(line, @"\|");
ListViewItem item = listview.Items.Add(values[0]);
if (values.Count() > 1)
{
for (int c = 1; c < values.Count(); c ++)
{
item.SubItems.Add(values[c]);
}
}
}
sr.Close();
}
}
这篇关于将我们的数据列表视图存储到Excel时，Excel工作表会自动保存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
