Building Loot Tables from Excel using the Content Pipeline

My current game project has a big emphasis on loot collection. I want my loot drop system to have a lot of flexibility for tuning drop rates though, so I’ve been creating a system similar to that found in Diablo II. I’ve settled on a system similar to the one described (in great detail) here, although considerably simplified for my purposes. What it boils down to is a table of values that I can easily add/remove entries from as well as tune any given values. Maintaining this type of data is where a spreadsheet really excels (ha!). But I need this data at runtime…

Enter the content pipeline. While the content pipeline has many built-in importers and processors, Excel spreadsheets are not covered. It was a relatively simple procedure to write my own importer and custom processor though. I could have settled for using handy intermediate XML and edited that by hand, but being able to work directly in Excel is much more convenient (and powerful).

So here’s what I’m going to show you: how to import data from excel, then how I processed it for my needs, and finally how to write it out to binary and read it back in at runtime.

What:

Create a custom importer and processor so that the content pipeline can build data from Excel files.

Why:

  • Data is much easier to create, edit and maintain in Excel compared to intermediate XML.
  • Data can be verified at build time using this method, as compared to reading in the data from un-built .xls or .csv at runtime
  • Spreadsheet source data can be imperfect (e.g. contain blanks or invalid entries) but the performance hit of processing this is taken at build-time, not at load time for the user.
  • The content pipeline can take advantage of the System.Data namespace, which isn’t available on Xbox360.

Background:

First off, here’s what I’m working towards getting at runtime:

public class TreasureClass
{
    public String Name;
    public int NumPicks;
    public int ChanceNoDrop;
    public Dictionary<String, int> Items;
    public int TotalProbability;
}

It’s a pretty simple class that basically represents a list of potential drops and their probabilities. A potential drop could be another TreasureClass (allowing high TreasureClasses to have a chance to drop items from a lower class) or an actual item. To drop from a TreasureClass, you just roll a random number and see which Item it corresponds to. If the item is another TreasureClass, repeat the process using that TreasureClass. Once you hit an actual item, drop it.

Simple, but powerful. It’s also very easy to change behaviour by adding new TreasureClasses or modifying probabilities.

So that’s what I’d like to end up with in game (a collection of TreasureClass objects). However, this is how I want to edit them (simplified a bit):

Name             Picks    NoDrop    TC1         ChanceTC1   TC2            ChanceTC2
Act 1 Equip A    1                  weapon03    50          armor03        50
Act 1 Equip B    1                  weapon06    50          armor06        50
Act 1 Equip C    1                  weapon09    50          armor09        50
Act 1 Melee A    1        100       Gold        30          Act 1 Equip A  30

It may not make a lot of sense if you haven’t read into how Diablo II’s loot tables work, but here’s the important part: data is stored in an Excel worksheet where each row corresponds to a TreasureClass.

Step 1: Content Importer

In my game’s ContentPipelineExtension library, I added a new class called XLSXImporter. This will read in the entire contents of the XSLX file into a DataSet object, which is then passed on to the custom processor. By writing it this way, I can create other processors that consume DataTables and thus use Excel for various types of data without having repeat all the file IO and worksheet parsing. In theory I could also re-use the processor with any importer that outputs a DataSet (such as a plain .csv importer for example).

Thankfully, we can make use of some handy System.Data classes since the content pipeline will be running on Windows. This makes grabbing all the data from an Excel file pretty trivial:

using System;
using System.Data;
using Microsoft.Xna.Framework.Content.Pipeline;
using System.Data.OleDb;

using TImport = System.Data.DataSet;

namespace ZomContentPipeline
{
    [ContentImporter(".xslx", DisplayName = "XSLX DataSet Importer", DefaultProcessor = "LootTableProcessor")]
    public class XLSXImporter : ContentImporter<TImport>
    {
        public override TImport Import(string filename, ContentImporterContext context)
        {
            DataSet dataSet = new DataSet();
            OleDbConnection connection = new OleDbConnection();
            OleDbDataAdapter dataAdapter;

            //For .xslx files, not .xls
            connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                            + filename + ";Extended Properties='Excel 8.0'";

            connection.Open();

            //Get the table schema, so we can programmitically grab all the sheets by name
            DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow row in schemaTable.Rows)
            {
                String sheetName = row["TABLE_NAME"].ToString();

                dataAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", connection);

                //Add the table to the dataset, giving it the appropriate name
                dataAdapter.Fill(dataSet, sheetName);
            }

            return dataSet;
        }
    }
}

That’s all there is to importing. This will consume an .xslx file and output a DataSet containing one DataTable per sheet. I’m grabbing all sheets out of convenience, but you could limit this to a particular sheet if you wanted to. However it doesn’t look like you can have parameters with Content Importers, so you can’t specify the sheet name as a parameter in the VS IDE Properties pane. Oh well.

Step 2: Process the data

Now we have the data from the Excel sheet in object form, but the game won’t be working with a DataSet. The next step is to write a processor that will make use of the DataSet from the importer and create the TreasureClass objects that I want to use in game. Much of this is custom code for my particular needs, but this is what it looks like:

using TInput = System.Data.DataSet;
using TOutput = System.Collections.Generic.List<ZomLib.Items.TreasureClass>;

namespace ZomContentPipeline
{
    [ContentProcessor(DisplayName = "Loot Table Processor")]
    public class LootTableProcessor : ContentProcessor<TInput, TOutput>
    {
        protected String sheetName;

        [DisplayName("Sheet Name")]
        [DefaultValue("Sheet1")]
        [Description("Name of the sheet in the .xslx to process")]
        public String SheetName
        {
            get { return sheetName; }
            set { sheetName = value; }
        }

        public override TOutput Process(TInput input, ContentProcessorContext context)
        {
            DataTable table = input.Tables[sheetName + "$"];

            List<TreasureClass> treasureClasses = new List<TreasureClass>();

            //name picks nodrop tc1 chance1 tc2 chance2 tc3 chance3 tc4 chance4 tc5 chance5
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    string name = (String)row["Name"];
                    TreasureClass tc = new TreasureClass(name);

                    int picks = 0;
                    int.TryParse(row["Picks"].ToString(), out picks);
                    tc.NumPicks = picks;

                    int noDrop = 0;
                    int.TryParse(row["NoDrop"].ToString(), out noDrop);
                    tc.ChanceNoDrop = noDrop;

                    for (int i = 1; i <= 5; i++)
                    {
                        AddTC(i, tc, row, context);
                    }

                    tc.UpdateTotalProbability();

                    treasureClasses.Add(tc);
                }
                catch (Exception) {}
        }

        int rowsWithErrors = nonEmptyRows - treasureClasses.Count;

        context.Logger.LogImportantMessage("Loot table complete");
        context.Logger.LogImportantMessage("TCs added: {0}", treasureClasses.Count);
        context.Logger.LogImportantMessage("Rows with errors: {0}", rowsWithErrors);

        return treasureClasses;
    }

    protected void AddTC(int tcNum, TreasureClass tc, DataRow row, ContentProcessorContext context)
    {
        String itemName = "TC" + tcNum;
        String chanceName = "ChanceTC" + tcNum;

        if ((row[itemName] is String && (!String.IsNullOrEmpty((String)row[itemName]))))
        {
            string item = (String)row[itemName];
            int chance = 0;
            int.TryParse(row[chanceName].ToString(), out chance);
            tc.AddItem(item, chance);
       }
    }
  }
}

I removed a bit of the error checking and some using statements to condense this a bit. The only interesting part here is that I did add a parameter called “Sheet Name” to the process. This allows me to specify the name of the sheet in the Excel file (well, technically the table name of the DataSet) to process. This saves me making an assumption that the data lives in the first table or the table called “Sheet1$”, etc. Other than that, it’s just some basic parsing to create a List of TreasureClass objects.

Step 3: Write out the game object data to binary, then read it from the game itself

At the end of the custom processor, you’ll note that we are left with a List of TreasureClass objects in memory, in the content pipeline. That’s of no use to the game itself. We need to write out that list of objects to an .xnb file. Then we need to read it back in at runtime.

Fortunately, XNA does this automagically for us! See here for more info on what’s happening. The short story is that the content pipeline can do this using reflection when we don’t create a custom writer/reader pair of our own.

Wrap-up:

That’s all there is to it. The content pipeline is a powerful tool that I’m only slowly starting to understand. Being able to maintain my data in Excel is going to be extremely useful and was well worth the effort to get this set up. In the end, it really only took me about an hour to get everything working as desired.