问题描述
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var cells = sheet.Descendants();
var myrows = sheet.Descendants();
string[] columns = new string[] { "POC Sworn Document Reviewer Name", "# of POC Sworn Docs Reviewed", "Total # of Hours Spent on Total POC Sworn Docs Reviewed for the Month", "Commentary", "Submitter Name on behalf of Firm", "Submitter Email address on behalf of Firm" };
int totalrowcount = dt.Rows.Count;
int NFirstRowNum = 5;
int NlastRowNum = totalrowcount + 5;
int NRowCount = 0;
foreach (Row newrow in sheet.Descendants<Row>().Where(r => r.RowIndex.Value >= NFirstRowNum && r.RowIndex.Value <= NlastRowNum))
{
Console.WriteLine("newrow " + newrow.ToString());
uint newIndex = (newrow.RowIndex + 1);
string curRowIndex = newrow.RowIndex.ToString();
string newRowIndex = newIndex.ToString();
Console.WriteLine("curRowIndex " + curRowIndex.ToString());
Console.WriteLine("newRowIndex " + newRowIndex.ToString());
foreach (DataRow dsrow in dt.Rows)
{
object col1 = dsrow[0].ToString();
object col2 = dsrow[1].ToString();
object col3 = dsrow[2].ToString();
object col4 = dsrow[3].ToString();
object col5 = dsrow[4].ToString();
object col6 = dsrow[5].ToString();
foreach (Cell dtc in newrow)
{
string s = dtc.CellReference.ToString();
//strip the nubmer from A4,B4,C4 etc
string result = Regex.Replace(s, "[^a-zA-Z]", "");
switch (result) //based on your columns
{
case "A":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col1.ToString());
break;
case "B":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col2.ToString());
break;
case "C":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col3.ToString()); //
break;
case "D":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col4.ToString()); //
break;
case "E":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col5.ToString());
break;
case "F":
dtc.DataType = new EnumValue<CellValues>(CellValues.String);
dtc.CellValue = new CellValue(col6.ToString());
break;
default:
break;
} // switch
} // foreach (DataRow dsrow in dt.Rows)
}// foreach (cell dtc in newrow)
} // foreach (Row newrow
spreadSheet.SaveAs("c:\\Monthly\\POC.xlsx");
spreadSheet.Close();
} //using
标签: c#excelopenxml-sdk
解决方案
推荐阅读