If you want to map the columns of json and xml file and then convert it to .txt file, Below is the code
private static void APIJsonData()
{
string json = File.ReadAllText(@"C:\SourceCode\Files\JsonFiles\jsondata.json");
var dataset = ReadDataFromJson(json, XmlReadMode.InferTypedSchema);
string xmlFileName = @"C:\SourceCode\Files\XmlFiles\" + "jsontoxml" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xml";
DataSet ds = new DataSet();
dataset.WriteXml(xmlFileName);
XmlDocument doc1 = new XmlDocument();
doc1.Load(xmlFileName);
using (StreamWriter sw = new StreamWriter(@"C:\SourceCode\Files\TextFiles\" + "xmltoTxt" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".txt"))
{
PrintNode(doc1.ChildNodes[1], sw);
sw.Close();
}
var stringjsonData = json;
}
private static void PrintNode(XmlNode xNode, StreamWriter writer){
bool printComma=false;int Cnt = 0;
// Check if the current node has just one level of child nodes
if (xNode.ChildNodes[0].ChildNodes.Count == 1){
//print the child nodes
writer.Write("'");
foreach (XmlNode chNode in xNode.ChildNodes)
{
Cnt++;
printComma = false;
XmlNodeList innerNodeList = chNode.ChildNodes;
if (innerNodeList.Count > 1)
{
if (chNode.Name != chNode.PreviousSibling.Name)
{
printComma = true;
writer.Write(writer.NewLine);
foreach (XmlNode innerNode in innerNodeList)
{
writer.Write(chNode.Name + "."+ innerNode.Name + " | ");
}
}
}
else
{
printComma = true;
writer.Write(xNode.Name +"."+ chNode.Name);
}
if (Cnt != xNode.ChildNodes.Count)
{
if (printComma == true)
{
writer.Write(",");
}
}
}
writer.Write(writer.NewLine);
}
else
{
foreach (XmlNode chNode in xNode.ChildNodes)
{
// recursively calling the method to get to the Node where the node will have only one level of child nodes.
PrintNode(chNode, writer);
}
}
////////////////////////////////////////////////////////
int count = 0;
// Check if the current node has just one level of child nodes
if (xNode.ChildNodes[0].ChildNodes.Count == 1)
{
//print the child nodes
writer.Write("'");
foreach (XmlNode chNode in xNode.ChildNodes)
{
count++;
XmlNodeList innerNodeList = chNode.ChildNodes;
if (innerNodeList.Count > 1)
{
writer.Write(writer.NewLine);
foreach (XmlNode innerNode in innerNodeList)
{
writer.Write(innerNode.InnerText + " | ");
}
}
else
{
writer.Write(chNode.InnerText);
}
if (count != xNode.ChildNodes.Count)
{
writer.Write(",");
}
}
writer.Write(writer.NewLine);
}
else
{
foreach (XmlNode chNode in xNode.ChildNodes)
{
// recursively calling the method to get to the Node where the node will have only one level of child nodes.
PrintNode(chNode, writer);
}
}
}
If you want to display placeholder text in excel cell, so that user can know what should be entered and on entering the placeholder text goes and entered value will be shown, then you need to format cell as custom format and give below formula
General;General;[Color15]”dd-MMM-yyyy”
Below is the VB.Net code , if you want to do through coding
Protected Sub btn_Click(sender As Object, e As EventArgs) Using wb = New XLWorkbook() Dim ws = wb.AddWorksheet(“Sheet1”) Dim cell = ws.FirstCell() cell.Value = 0.0 ‘cell.DataType = XLDataType.DateTime cell.Style.NumberFormat.Format = BuildWatermarkFormat(“dd-MMM-yyyy”) ‘”_ * # ##0.00_ ;_ * -# ##0.00_ ;_ * “”-“”??_ ;_ @_ “ ‘cell.Style.NumberFormat.SetNumberFormatId(43) ‘cell.Value = 0 ws.Columns(“A”).AdjustToContents() wb.SaveAs(“d:\test.xlsx”) End Using End Sub
Public Function BuildWatermarkFormat(ByVal watermarkText As String, Optional ByVal positiveFormat As String = “General”, Optional ByVal negativeFormat As String = “General”, Optional ByVal textFormat As String = “General”) As String BuildWatermarkFormat = positiveFormat & “;” & negativeFormat & “;[Color15]” & Chr(34) & watermarkText & Chr(34) & “;” & textFormat End Function
Navilu 