在excel中存储字符串数组值 [英] Storing string array values in excel
本文介绍了在excel中存储字符串数组值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在excel sheet中存储字符串数组值。这是代码，值只存储在excel的第7列。
private void Download_Click（object sender，EventArgs e）
{
try
{
Excel.Application xlApp = default（Excel.Application）;
Excel.Workbook xlWorkBook = default（Excel.Workbook）;
Excel.Worksheet xlWorkSheet = default（Excel.Worksheet）;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application（）;
xlWorkBook = xlApp.Workbooks.Add（misValue）;
xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）;
xlWorkSheet.Cells [1,1] =机器ID;
xlWorkSheet.Cells [1,2] =客户ID;
xlWorkSheet.Cells [1,3] =姓名;
xlWorkSheet.Cells [1,4] =总余额;
xlWorkSheet.Cells [1,5] =付费金额;
xlWorkSheet.Cells [1,6] =最后付款日期;
xlWorkSheet.Cells [1,7] =到期金额;
xlWorkSheet.Cells [1,1] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,2] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,3] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,4] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,5] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,6] .Interior.ColorIndex = 39;
xlWorkSheet.Cells [1,7] .Interior.ColorIndex = 39;
serialPort1.DataReceived + = new SerialDataReceivedEventHandler（serialPort1_DataReceived）;
string [] lines = Regex.Split（s，[$＃，]）;
// string [] lines = s.Split（'，'）;
//MessageBox.Show（split Value：+ line）;
字符串行;
for（int p = 1; p< lines.Length; p ++）
{
for（int q = 1; q< 7; q ++ ）
{
line = lines [q];
xlWorkSheet.Cells [p，q] = lines;
if（line！=）
（（Microsoft.Office.Interop.Excel.Range）xlWorkSheet.Cells [p + 1，q + 1]）。Value2 = lines [p];
}
}
xlWorkBook.SaveAs（fName，Excel.XlFileFormat.xlWorkbookNormal，misValue，misValue，misValue，misValue，Excel.XlSaveAsAccessMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）;
xlWorkBook.Close（true，misValue，misValue）;
xlApp.Quit（）;
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
}
catch（例外p）
{
MessageBox.Show（p.StackTrace）;
}
最后
{
if（xlApp！= null）
releaseObject（xlApp）;
if（xlWorkBook！= null）
releaseObject（xlWorkBook）;
if（xlWorkSheet！= null）
releaseObject（xlWorkSheet）;
}
if（System.IO.File.Exists（fName））
{
if（MessageBox.Show（你想打开excel文件吗？，这个.Text，MessageBoxButtons.YesNo，MessageBoxIcon.Question）== DialogResult.Yes）
{
try
{
System.Diagnostics.Process.Start（fName）;
}
catch（exception ex）
{
MessageBox.Show（打开excel文件时出错。+ Environment.NewLine +
ex.Message，this。 Text，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
}
}
解决方案
< blockquote>＃，]）;
// string [] lines = s.Split（'，'）;
//MessageBox.Show (\"split Value： + line）;
String line;
for（int p = 1; p< lines.Length; p ++）
{
for （int q = 1; q< 7; q ++）
{
line = lines [q];
xlWorkSheet.Cells [p，q] = lines;
if（b） line！=）
（（Microsoft.Office.Interop.Excel.Range）xlWorkSheet.Cells [p + 1，q + 1]）。Value2 = lines [p];
}
}
xlWorkBook.SaveAs （fName，Excel.XlFileFormat.xlWorkbookNormal，misValue，misValue，misValue，misValue，Excel.XlSaveAsAcc essMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）;
xlWorkBook.Close（true，misValue，misValue）;
xlApp.Quit（）;
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
}
catch（例外p）
{
MessageBox.Show（p.StackTrace）;
}
最后
{
if（xlApp！= null）
releaseObject（xlApp）;
if（xlWorkBook！= null）
releaseObject（xlWorkBook）;
if（xlWorkSheet！= null）
releaseObject（xlWorkSheet）;
}
if（System.IO.File.Exists（fName））
{
if（MessageBox.Show（你想打开excel文件吗？，这个.Text，MessageBoxButtons.YesNo，MessageBoxIcon.Question）== DialogResult.Yes）
{
try
{
System.Diagnostics.Process.Start（fName）;
}
catch（exception ex）
{
MessageBox.Show（打开excel文件时出错。+ Environment.NewLine +
ex.Message，this。 Text，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
}
}
> for （ int p = 1 ; p < = lines.Length; p ++）
{
for （ int q = 1 ; q < = 7 ; q ++）
{
blahh .... blah .... blah ....
}
}
hi,
am storing string array values in excel sheet.here is the code with this , values are storing at only in 7 th column of excel.
private void Download_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp = default(Excel.Application);
Excel.Workbook xlWorkBook = default(Excel.Workbook);
Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Machine ID";
xlWorkSheet.Cells[1, 2] = "Customer ID";
xlWorkSheet.Cells[1, 3] = "Name";
xlWorkSheet.Cells[1, 4] = "Total Balance";
xlWorkSheet.Cells[1, 5] = "Paid Amount";
xlWorkSheet.Cells[1, 6] = "Last Paid Date";
xlWorkSheet.Cells[1, 7] = "Due Amount";
xlWorkSheet.Cells[1, 1].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 2].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 3].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 4].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 5].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 6].Interior.ColorIndex = 39;
xlWorkSheet.Cells[1, 7].Interior.ColorIndex = 39;
serialPort1.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);
string[] lines = Regex.Split(s, "[$#,]");
// string[] lines = s.Split(',');
//MessageBox.Show("split Value:" + line);
String line;
for (int p = 1; p < lines.Length; p++)
{
for (int q = 1; q < 7; q++)
{
line=lines[q];
xlWorkSheet.Cells[p, q] = lines;
if(line!="")
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[p + 1, q+1 ]).Value2 = lines[p];
}
}
xlWorkBook.SaveAs(fName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception p)
{
MessageBox.Show(p.StackTrace);
}
finally
{
if (xlApp != null)
releaseObject(xlApp);
if (xlWorkBook != null)
releaseObject(xlWorkBook);
if (xlWorkSheet != null)
releaseObject(xlWorkSheet);
}
if (System.IO.File.Exists(fName))
{
if (MessageBox.Show("Would you like to open the excel file?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process.Start(fName);
}
catch (Exception ex)
{
MessageBox.Show("Error opening the excel file." + Environment.NewLine +
ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
解决方案
#,]"); // string[] lines = s.Split(','); //MessageBox.Show("split Value:" + line); String line; for (int p = 1; p < lines.Length; p++) { for (int q = 1; q < 7; q++) { line=lines[q]; xlWorkSheet.Cells[p, q] = lines; if(line!="") ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[p + 1, q+1 ]).Value2 = lines[p]; } } xlWorkBook.SaveAs(fName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } catch (Exception p) { MessageBox.Show(p.StackTrace); } finally { if (xlApp != null) releaseObject(xlApp); if (xlWorkBook != null) releaseObject(xlWorkBook); if (xlWorkSheet != null) releaseObject(xlWorkSheet); } if (System.IO.File.Exists(fName)) { if (MessageBox.Show("Would you like to open the excel file?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { System.Diagnostics.Process.Start(fName); } catch (Exception ex) { MessageBox.Show("Error opening the excel file." + Environment.NewLine + ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); } } } }
for (int p = 1; p <= lines.Length; p++) { for (int q = 1; q <= 7; q++) { blahh....blah....blah.... } }
这篇关于在excel中存储字符串数组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
